Kristen recently asked why his msdb database had grown so large (750MB). He found out that it was due to the sizes of the backup system tables. These tables store information used for backup and restore operations. If you've ever used the restore database wizard in Enterprise Manager (and if you have, you might want to start using Query Analyzer instead to get more familiar with backup and restore operations), you might notice that it displays a listing of previous backups to choose from for the restore. It gathers this information from these backup system tables. If you backup your transaction logs as often as we do (every 15 minutes), these tables can get quite large. Microsoft provides sp_delete_backuphistory to delete this data. As Kristen found out, this stored procedure is very slow as it uses a cursor to loop through the data. Using sp_delete_backuphistory took over 2 hours to delete all but 10 days worth of data. I provided to him my version of this stored procedure that uses JOINs instead of a cursor. With slight modifications, he was able to get it working for him. This version was able to delete all but 10 days worth of data in 32 seconds. Here's the stored procedure:
CREATE PROC isp_DeleteBackupHistory
(@DaysToRetain int)
AS
SET NOCOUNT ON
DECLARE @Err int
DECLARE @rc int
BEGIN TRAN
DELETE FROM msdb..restorefile
FROM msdb..restorefile rf
INNER JOIN msdb..restorehistory rh
ON rf.restore_history_id = rh.restore_history_id
INNER JOIN msdb..backupset bs
ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)
SET @Err = @@ERROR
IF @Err <> 0
GOTO Error_Exit
DELETE FROM msdb..restorefilegroup
FROM msdb..restorefilegroup rfg
INNER JOIN msdb..restorehistory rh
ON rfg.restore_history_id = rh.restore_history_id
INNER JOIN msdb..backupset bs
ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)
SET @Err = @@ERROR
IF @Err <> 0
GOTO Error_Exit
DELETE FROM msdb..restorehistory
FROM msdb..restorehistory rh
INNER JOIN msdb..backupset bs
ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)
SET @Err = @@ERROR
IF @Err <> 0
GOTO Error_Exit
SELECT media_set_id, backup_finish_date
INTO #Temp
FROM msdb..backupset
WHERE backup_finish_date < (GETDATE() - @DaysToRetain)
SET @Err = @@ERROR
IF @Err <> 0
GOTO Error_Exit
DELETE FROM msdb..backupfile
FROM msdb..backupfile bf
INNER JOIN msdb..backupset bs
ON bf.backup_set_id = bs.backup_set_id
INNER JOIN #Temp t
ON bs.media_set_id = t.media_set_id
WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)
SET @Err = @@ERROR
IF @Err <> 0
GOTO Error_Exit
DELETE FROM msdb..backupset
FROM msdb..backupset bs
INNER JOIN #Temp t
ON bs.media_set_id = t.media_set_id
SET @Err = @@ERROR
IF @Err <> 0
GOTO Error_Exit
DELETE FROM msdb..backupmediafamily
FROM msdb..backupmediafamily bmf
INNER JOIN msdb..backupmediaset bms
ON bmf.media_set_id = bms.media_set_id
INNER JOIN #Temp t
ON bms.media_set_id = t.media_set_id
SET @Err = @@ERROR
IF @Err <> 0
GOTO Error_Exit
DELETE FROM msdb..backupmediaset
FROM msdb..backupmediaset bms
INNER JOIN #Temp t
ON bms.media_set_id = t.media_set_id
SET @Err = @@ERROR
IF @Err <> 0
GOTO Error_Exit
COMMIT TRAN
SET @rc = 0
GOTO isp_DeleteBackupHistory_Exit
Error_Exit:
ROLLBACK TRAN
SET @rc = -1
isp_DeleteBackupHistory_Exit:
DROP TABLE #Temp
SET NOCOUNT OFF
RETURN @rc
GO