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 
.
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 |
 This 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 
. Myself successful reduce MSDB size from 8 GB become 1.5 GB with 3 months retention data.
