Purge MSDB Backup History in chunks
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