Sometimes a DBA goes through such situations where MSDB
grows too large and MSDB’s huge historical data becomes the cause for poor
performance of maintenance, storage and scheduled alerts. There DBA is required
to clean up the MSDB.
MSDB tables contain SQL Server Agent Jobs, Database Mail
, Alerts, Operators configurations and Maintenance Plans, Online Database Backups-Restore and Scheduled tasks
History.
SQL Server Maintenance Plan GUI contains a task History Cleanup Task
Step 1: Server -> Management -> Maintenance Plans ->Maintenance Plan -> Right Click -> Maintenance Plan Wizard
Step 2: Click on -> Next
Step 3: Choose schedule type -> Change Schedule and Click -> Next
Step 4: Choose Clean Up History -> Next
Step 5: Clean Up History -> Next
Step 6: Choose historical data to be deleted -> Choose Older than One Month -> Next
Step 7: Click Finish to Complete the Wizard
Step 8: Click Close once actions finished successfully
Step 8: Server -> SQL Server Agent -> Jobs -> Find newly created Plan
Method 2:
MSDB cleansing through TSQL
Step 1: Find and analyze MSDB tables data sizes
/*-------------------------------- Find the MSDB Tables with Largest Data ------------------------- */ SELECT sc.name + '.' + t.NAME AS TableName, p.[Rows], ( SUM(a.total_pages) * 8 ) / 1024 AS TotalReservedSpaceMB, -- Number of total pages * 8KB size of each page in SQL Server ( SUM(a.used_pages) * 8 ) / 1024 AS UsedDataSpaceMB, ( SUM(a.data_pages) * 8 ) / 1024 AS FreeUnusedSpaceMB FROM msdb.sys.tables t INNER JOIN msdb.sys.schemas sc ON sc.schema_id = t.schema_id INNER JOIN msdb.sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN msdb.sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN msdb.sys.allocation_units a ON p.partition_id = a.container_id WHERE t.type_desc = 'USER_TABLE' AND i.index_id <= 1 --- Heap\ CLUSTERED GROUP BY sc.name + '.' + t.NAME, i.[object_id],i.index_id, i.name, p.[Rows] ORDER BY ( SUM(a.total_pages) * 8 ) / 1024 DESC
Step 2 : Now figure out table with largest data size belongs to which feature of MSDB and clean up them.In my case, I had found heavy size tables in MSDB that relates to 'Backup and Restore History', 'Database Mail Log' and 'Maintenance Plans Log'.
/*---------------------------------- Clean "Backup and Restore History" MSDB tables ---------------- */ --- Backup and Restore Tables SELECT * FROM msdb..backupfile SELECT * FROM msdb..backupfilegroup SELECT * FROM msdb..backupmediafamily SELECT * FROM msdb..backupmediaset SELECT * FROM msdb..backupset SELECT * FROM msdb..restorefile SELECT * FROM msdb..restorefilegroup SELECT * FROM msdb..restorehistory --- Execute system stored procedure to reduces the size of backup and restore history tables --- sp_delete_backuphistory @oldest_date EXECUTE msdb..sp_delete_backuphistory '1-OCT-2013' -- Delete entries older than OCT 2013
/*---------------------------------- Clean "Database Mail Log" MSDB tables ------------------------- */ --- Database Mail Log Tables SELECT * FROM msdb..sysmail_faileditems SELECT * FROM msdb..sysmail_unsentitems SELECT * FROM msdb..sysmail_sentitems SELECT * FROM msdb..sysmail_allitems SELECT * FROM msdb..sysmail_log --- Execute system stored procedures to reduces the size of e-mail messages and log tables --1.--e-mail messages-- --- sysmail_delete_mailitems_sp @sent_before , @sent_status EXECUTE msdb..sysmail_delete_mailitems_sp 'October 1, 2013',NULL -- Delete entries older than OCT 2013 --2.--LOG-- --- sysmail_delete_log_sp @logged_before,@event_type EXECUTE msdb..sysmail_delete_log_sp 'October 1, 2013',NULL -- Delete entries older than OCT 2013
/*---------------------------------- Clean "Maintenance Plan Log" MSDB tables ------------------------- */ --- Maintenance Plan Log Tables SELECT * FROM msdb..sysmaintplan_logdetail; SELECT * FROM msdb..sysmaintplan_log; --- Execute system stored procedures to reduces the size maintenance plan tables --Undocumented SP --- sp_maintplan_delete_log @plan_id, @subplan_id, @oldest_time EXECUTE msdb..sp_maintplan_delete_log NULL,NULL,'2013-10-16T00:00:00' -- Delete entries older than OCT 2013 --Alternatively: Truncate Maintenance Plan Log Tables DELETE --SELECT * FROM msdb..sysmaintplan_logdetail; --------------------------- DELETE --SELECT * FROM msdb..sysmaintplan_log;
Step3: Release occupied but unused space from MSDB tables
/*---------------------------------- Release Occupied Spaces ----------------------------------------- */ DBCC SHRINKDATABASE ('MSDB')
Reference Links
Archive Database Mail: http://technet.microsoft.com/en-us/library/ms189258.aspx