DBA and SysAdmin World

July 19, 2008

Clean up MSDB

Filed under: Maintenance — Tags: , , — solihinho @ 8:33 AM

Clean Up Problem
I am facing a problem with MSDB database. Right now its size about 8 GB. As we know MSDB is SQL server system database beside master, model and tempdb used for keep backup and restore database log. SQL Server 2000 have provided “sp_delete_backup_and_restore_history” or “sp_delete_backuphistory” on SQL Server 2005. Both SP can be found in MSDB. When I tried use these SP especially “sp_delete_backup_and_restore_history” on our development server, there is no issue on it. But when tried to production which is larger than development, the issue occurred. It take about 48 minutes just for deleting 1 day history data Doh.

Solution
I did a modification on the code such as using looping, inner join etc. Every 1 cycle of loop, the script trying to delete 1000 rows of backupset table as master table.

usage :
    exec dbo.spDeleteBackupRestoreHistory 60 which is mean we just want keep 60 day history data on backupset table

script :

/*
    created by : solihin ho - July 2008 - www.solihinho.wordpress.com
    compatibility : SQL Server 2000 and SQL Server 2005
*/ 

CREATE PROC spDeleteBackupRestoreHistory (@RetentionDay INT)
AS 

SET XACT_ABORT ON
SET NOCOUNT ON 

DECLARE @OldDate DateTime,
        @RowCount INT 

SELECT @OldDate = DateAdd(dd, @RetentionDay * -1, GetDate()) 

IF OBJECT_ID('tempdb..#backup') IS NOT NULL
   DROP TABLE #backup 

CREATE TABLE #backup
(
   backup_set_id int PRIMARY KEY,
   media_set_id int,
   is_exist char(1) DEFAULT 'N'
) 

WHILE (1 = 1)
BEGIN 

    TRUNCATE TABLE #backup 

    -- 'Preparing...'
    INSERT INTO #backup(backup_set_id, media_set_id)
       SELECT TOP 1000 backup_set_id, media_set_id
       FROM msdb.dbo.backupset bs
       WHERE bs.backup_finish_date < @OldDate
       ORDER BY bs.backup_set_id 

    SET @RowCount = @@RowCount 

    IF @RowCount = 0
        GOTO _EXIT 

    BEGIN TRAN

    -- 'delete table restorefile'
    DELETE FROM msdb.dbo.restorefile
    FROM msdb.dbo.restorefile rf
    INNER JOIN msdb.dbo.restorehistory rh
        ON rf.restore_history_id = rh.restore_history_id
    INNER JOIN #backup bs
        ON rh.backup_set_id = bs.backup_set_id

    -- 'delete restorefilegroup'
    DELETE FROM msdb.dbo.restorefilegroup
    FROM msdb.dbo.restorefilegroup rfg
    INNER JOIN msdb.dbo.restorehistory rh
        ON rfg.restore_history_id = rh.restore_history_id
    INNER JOIN #backup bs
        ON rh.backup_set_id = bs.backup_set_id

    -- 'delete restorehistory'
    DELETE FROM msdb.dbo.restorehistory
    FROM msdb.dbo.restorehistory rh
    INNER JOIN #backup bs
        ON bs.backup_set_id = rh.backup_set_id

    -- 'delete backupfile'
    DELETE FROM msdb.dbo.backupfile
    FROM msdb.dbo.backupfile bf
    INNER JOIN #backup bs
        ON bs.backup_set_id = bf.backup_set_id

    -- 'delete backupset'
    DELETE FROM msdb.dbo.backupset
    FROM msdb.dbo.backupset bs
    INNER JOIN #backup b
        ON bs.backup_set_id = b.backup_set_id

    -- 'search media_set_id that not exists to backupset'
    UPDATE #backup
    SET is_exist = 'Y'
    FROM #backup b
    INNER JOIN msdb.dbo.backupset bs
            ON bs.media_set_id = b.media_set_id

    -- 'delete backupmediafamily'
    DELETE FROM msdb.dbo.backupmediafamily
    FROM msdb.dbo.backupmediafamily bmf
    INNER JOIN #backup b
        ON bmf.media_set_id = b.media_set_id
    WHERE b.is_exist = 'N'

    -- 'delete backupmediaset'
    DELETE FROM msdb.dbo.backupmediaset
    FROM msdb.dbo.backupmediaset bms
    INNER JOIN #backup b
        ON bms.media_set_id = b.media_set_id
    WHERE b.is_exist = 'N'

    COMMIT TRAN 

END

_EXIT:
   DROP TABLE #backup

Still have performance issue ?
Perhaps you still have an issue with the performance, just alter the SP and comment the script  from “search media_set_id that not exists to backupset” until “delete backupmediaset”. We can delete backupmediafamily and backupmediaset table lately. After you run the script until determined retention period, you just run below script to delete those 2 tables

DELETE FROM msdb.dbo.backupmediafamily
   FROM msdb.dbo.backupmediafamily bmf
   LEFT OUTER JOIN msdb.dbo.backupset b ON b.media_set_id = bmf.media_set_id
   WHERE b.media_set_id IS NULL

DELETE FROM msdb.dbo.backupmediaset
   FROM msdb.dbo.backupmediaset bmf
   LEFT OUTER JOIN msdb.dbo.backupset b ON b.media_set_id = bmf.media_set_id
   WHERE b.media_set_id IS NULL
LoserThis performance issue caused by when we want to delete backupmediafamily and backupmediaset need to join to backupset table base on media_set_id column. The rule is we can delete the data on these tables if the media_set_id does not exists anymore in backupset table. The problem is there is no index base on this column on backupset.

Best Practice
After you finish clean up the MSDB until one retention period, just schedule that script whether weekly or even daily up to your backup and restore transaction. Should be there is no issue on MSDB size Cool. Myself successful reduce MSDB size from 8 GB become 1.5 GB with 3 months retention data.

Advertisements

Blog at WordPress.com.