Kentico DB Cleanse

kentico-db-cleanse.sql

-- Note: replace UserPrivilegeLevel = 3 with UserIsGlobalAdministrator = 1 for K9 and lower.

-- Sanitize Settings
UPDATE CMS_SettingsKey SET KeyValue = NULL WHERE KeyName in (
    'CMSAdminEmailAddress',                 -- Clear the Administrator email
    'CMSUsePasswordPolicy',                 -- Clear the password policy
    'CMSUseSSLForAdministrationInterface',  -- Clear 'SSL for administration interface'
    'CMSScreenLockEnabled',                 -- Clear screen lock feature
    'CMSSMTPServer',                        -- Clear sending e-mails
    'CMSDisableDebug'                       -- Clear 'Disable debugging'
)
 
-- (Optional) Re-enable useful defaults
-- UPDATE CMS_SettingsKey
--     SET KeyValue = CASE
--             WHEN KeyName = 'CMSAdminEmailAddress' THEN 'admin@localhost.local' -- Set the Administrator email
--             WHEN KeyName = 'CMSSMTPServer' THEN 'localhost'                    -- Set the SMTP server
--             ELSE KeyValue
--     END
  
-- Disable servers
UPDATE CMS_SMTPServer SET ServerEnabled = 0
UPDATE CMS_WebFarmServer SET ServerEnabled = 0
UPDATE CMS_SMTPServer SET ServerEnabled = 0
UPDATE Integration_Connector SET ConnectorEnabled = 0
UPDATE Staging_Server SET ServerEnabled = 0
 
-- Clear the password for the Administrator accounts
UPDATE CMS_User SET UserPassword = '', UserEnabled = 1 WHERE UserPrivilegeLevel = 3
SELECT UserID, UserName [Global administrator users] FROM CMS_User WHERE UserPrivilegeLevel = 3
   
-- Disable all global non-default scheduled tasks (note: this also disables all the site level tasks)
UPDATE CMS_ScheduledTask SET TaskEnabled = 0
WHERE TaskGUID IN (
'7F8B5F10-A843-433E-86B7-A4C9C8336328', -- Analytics.LogProcessing
'1B067823-DF3F-4E32-A520-35901F8501BB', -- Email.QueueSender
'F6A7D4DC-0146-4966-BE83-F7E3A87DF41C', -- Email.QueueCleaner
'4F1469A0-8F3F-42A2-9A08-72F27C225271', -- TimeZone.Recalculate
'3788D62F-378A-40ED-82B4-7C230E74D773', -- SessionsRemoveExpiredSessions
'C1C3045D-E252-4FB8-92E0-D9530BB944EC', -- SessionsUpdateDatabaseSession
'D931DDD7-9C2E-49B9-B92B-66D25282E1E7', -- ForumThreadViewsProcessor
'4E5FC15A-AA43-4C73-B8D8-24E65EA7F10D', -- Content.DeleteOldTemporaryAttachments
'FBD459F7-F0B2-4E4A-A392-EC980E0C35A0', -- Search.TaskExecutor
'501A6EE6-7E28-4474-82FF-E22C334FD5B5', -- Search.IndexOptimizer
'6FF7094C-17A0-4A2B-A67E-4FA38EDC80DB', -- System.UnusedMemoryCleaner
'27C692F2-C5B6-4D7B-B782-CC8BB7DF7463', -- NewsletterSender
'D4F09853-DDB9-45F8-9D30-A3698B40DC26', -- DeleteImageEditorHistory
'27824BFE-F16F-4431-949C-82FCFD15E3B7', -- RemoveAnalyticsData
'EEFACB2F-C4C8-48A1-9558-D1633E477AB8', -- Integration.ProcessExternalTasks
'EDF602FC-94A4-4AFE-B58F-C77966CB085A', -- EProductReminder
'54D610E2-75CE-40BE-8C37-4DE8B3DA254A', -- RentedLicenseUpdater
'7C93F262-FDAD-4571-A568-EB0AD882825D', -- Content.DeleteOldTemporaryUploadFiles
'AC98558E-201F-476E-862B-54EE24E97FD5', -- MembershipReminder
'5F085350-950D-42E5-86FE-F26BADE31AFB', -- DeleteInactiveContacts
'B679317A-AA15-4EAF-8F8C-7F5C14F9B7E5', -- ChatOldRecordsCleaner
'8E2DFDE4-C894-4984-94DA-3F9C81539E56', -- cleanchatonlineusers
'AD35CFC9-9FEC-4A6C-9A6E-D21A62683B5D', -- DeletedRoomsCleaner
'FC84271E-B158-4535-8416-4F940E360B1D', -- Report_subscription_sender
'F72E08CC-4E2D-4783-8817-D8D0DF0CEE42', -- ChatOldInitiatedChatRequestsCleaner
'99A15B0E-BC9B-4FB5-B047-315C56321ABD', -- Cache.DeleteOldFileSystemCache
'4BADF7AB-DFBD-4AFD-9A4C-C292969B790F', -- ModuleUsageTrackingTask
'92A77D69-93FF-4748-831D-9115938DB35C', -- CleanAzureStorageCache
'1B130887-8EB2-44D1-A1AA-AE350376D938', -- SalesForce.Replicate
'EEA7E53D-0FB3-40BD-826D-4678C46CFDA5') -- RecalculateCampaignReports
   
-- (Optional) Disable scheduler completely in the settings
-- UPDATE CMS_SettingsKey SET KeyValue = 'False' WHERE KeyGUID = 'E96E6A0C-491F-43FC-97B7-12C16E10B2A8'
  
-- Show database information
SELECT
  'Settings' AS ' ',
  (SELECT KeyValue FROM CMS_SettingsKey WHERE KeyName = 'CMSDBVersion') AS 'CMSDBVersion',
  (SELECT KeyValue FROM CMS_SettingsKey WHERE KeyName = 'CMSDataVersion') AS 'CMSDataVersion',
  (SELECT KeyValue FROM CMS_SettingsKey WHERE KeyName = 'CMSHotfixVersion') AS 'CMSHotfixVersion',
  (SELECT count(*) FROM CMS_WebFarmServer) AS 'Web farm servers',
  (SELECT count(*) FROM Staging_Server) AS 'Staging servers',
  (SELECT count(*) FROM CMS_SMTPServer) AS 'SMTP servers',
  (SELECT STUFF((SELECT ClassName + ' ' FROM CMS_Class WHERE ClassIsDocumentType = 0 AND ClassTableName NOT IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES)
                    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,0,'')) AS 'Classes with no table',
  (SELECT STUFF((SELECT TABLE_NAME + ' ' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME NOT IN (SELECT ClassTableName FROM CMS_Class WHERE ClassTableName IS NOT NULL)
                    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,0,'')) AS 'Tables with no class',
  (SELECT SERVERPROPERTY('COLLATION')) AS 'Server Collation',
  (SELECT DATABASEPROPERTYEX(DB_NAME(), 'COLLATION')) AS 'Database Collation'
  
SELECT
  'Trouble tables' AS ' ',
  (SELECT count(*) FROM CMS_WebFarmTask WHERE TaskCreated < DATEADD(hour, -24, GETDATE())) AS 'Web farm tasks',
  (SELECT count(*) FROM Staging_Task WHERE TaskTime < DATEADD(hour, -24, GETDATE())) AS 'Staging tasks',
  (SELECT count(*) FROM Integration_Task WHERE TaskTime < DATEADD(hour, -24, GETDATE())) AS 'Integration tasks',
  (SELECT count(*) FROM CMS_ScheduledTask WHERE [TaskDeleteAfterLastRun] = 1 AND [TaskNextRunTime] < DATEADD(hour, -24, GETDATE())) AS 'Scheduled tasks',
  (SELECT count(*) FROM CMS_SearchTask WHERE SearchTaskCreated < DATEADD(hour, -24, GETDATE())) AS 'Search tasks',
  (SELECT count(*) FROM CMS_Email WHERE EmailStatus = 1 AND EmailLastSendResult IS NOT NULL) AS 'Stuck emails'
  
-- Most common events
SELECT TOP(10) Count(EventDescription) AS Count, EventCode, Source, EventDescription, MIN(EventTime) AS 'Event First Date', MAX(EventTime) AS 'Event Last Date'
FROM CMS_EventLog
WHERE EventType = 'E' GROUP BY Source, EventCode, EventDescription
HAVING Count(EventDescription) > 5
ORDER BY Count DESC
  
-- Table statistics for typically large tables
DECLARE @largetables TABLE (Names VARCHAR(MAX))
INSERT INTO @largetables VALUES ('CMS_Attachment'),('CMS_AttachmentHistory'),('CMS_VersionHistory'),('CMS_ObjectVersionHistory'),('CMS_EventLog');
   
WITH tempstats AS
    (SELECT
        T.Name TableName,
        Sum (S.used_page_count) used_pages_count,
        Sum (CASE WHEN (I.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE (lob_used_page_count + row_overflow_used_page_count) END) pages
    FROM sys.Dm_db_partition_stats S
    JOIN sys.Tables T ON T.object_id = S.object_id
    JOIN sys.Indexes I ON I.object_id = T.object_id AND I.index_id = S.index_id
    WHERE T.Name IN (SELECT Names FROM @largetables)
    GROUP  BY T.Name)
       
SELECT
    Sizes.TableName [Large tables], Sizes.TableSizeInMb [Table Size In Mb], Sizes.IndexSizeInMb [Index Size In Mb], Rows.TableNumberOfRows [Table # of Rows]
FROM
    (SELECT Tempstats.TableName,
        Cast((Tempstats.pages * 8.) / 1024 AS DECIMAL(10, 3)) TableSizeInMb,
        Cast(((CASE WHEN Tempstats.used_pages_count > Tempstats.pages THEN Tempstats.used_pages_count - Tempstats.pages ELSE 0 END ) * 8. / 1024 ) AS DECIMAL(10, 3)) IndexSizeInMb
    FROM tempstats) Sizes
    JOIN (SELECT T.Name TableName, I.rows TableNumberOfRows FROM sys.Tables T JOIN sys.Sysindexes I ON T.object_id = I.id WHERE  indid IN ( 0, 1 )) Rows ON Sizes.TableName = Rows.TableName
ORDER  BY [Table # of Rows] DESC