A re-occurring issue im running into at work at present is finding extremely large MSDB databases with no purge history jobs on the instance, that have been around for years and many of which are SQL Server 2005. If you cant imagine what a 8-9 year old MSDB looks like, its not pretty! The problem is, if you try and run a sp_delete_backuphistory on 9 years of data, its pretty much guaranteed to fill up your MSDB log drive. The solution is to run the stored proc in small chunks to keep things at a sensible level. Below is my script to clear 1 day at a time, (you can do more if you like, but on a instance that had over 7 years it ran in 40 mins and the log file never expanded passed the 2gb i set it to).

 

/*

Clear Backup History in a loop 

*/

-- Find earliest date in backup table
DECLARE @date DATETIME 
SET @date = (SELECT  top (1) backup_start_date  FROM msdb..backupset order by backup_start_date)
PRINT @date

-- Define when to stop deleting backup history 
DECLARE @lastWeek DATETIME 
SET @lastWeek = GETDATE() - 90

-- Delete in Single Days? 
WHILE ( @date <= @lastWeek )
    BEGIN
        EXEC msdb..sp_delete_backuphistory @date 
	SET @date = DATEADD(DAY, +1, @date)
        PRINT @date
	CHECKPOINT
    END
Advertisements