Loading ...Applicable to SQL 2005 and 2008 (R2)
msdb system database stores backup/restore history information. On any server where there are lots of TRN log backup and restore operations (like a production server with 5 min trn log backup for tens or hundreeds databases) msdb database could get quite large and to clean backup/restore data that is old and not needed the following stored procedure is used:
sp_delete_backuphistory
Sample SP execution is shown below:
DECLARE @cut_off_date DATETIME
SET @cut_off_date = DATEADD(m, -6, getdate())
EXEC msdb.dbo.[sp_delete_backuphistory] @cut_off_date
The SP is used with a date as an input parameter indicating that all the data prior to specified date should be removed. Naturally this operation is very slow, takes a lot of time to remove few thousands rows not to mention millions also the SP causes many locks on key MSDB tables that are involved in the backup/restore process – the lock will cause TRN log backup or Full backup process to be blocked untill the SP finishes.
One of the ways to improve performance of the clean-up process and to make the SP run faster is to add indexes by running a script below:
USE [msdb]
GO
CREATE NONCLUSTERED INDEX idx_backupset_media_set_id
ON [dbo].[backupset] ([media_set_id])
USE [msdb]
GO
CREATE NONCLUSTERED INDEX idx_restorefile_restore_history_id
ON [dbo].[restorefile] ([restore_history_id])
USE [msdb]
GO
CREATE NONCLUSTERED INDEX idx_restorefile_restore_history_id
ON [dbo].[restorehistory] ([restore_history_id])
(3 votes, average: 3.67 out of 5)
