SQLCache: MSDB Purging

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.

Method 1:

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

Monday 9 December 2013

MSDB Purging