SQL Sysadmin : Clear Backup History (nibble delete)

It is best practice to remove old backup history records from MSDB. Else they will eventually cause MSDB to bloat and Management Studio to become unresponsive when dealing with BACKUPs and RESTOREs.

Note : Only do this when required backups are safely archived away.

Last year I blogged about this and included a script to trim backup history back to a month's data.
SQL Solace : Removing Database Backup History

If housekeeping has not been performed on backup history then many thousands of backup records may exist. For example ;
Say 47 log backups are taken each day, in addition to 1 full backup.
This happens for 10 databases, every day for 5 years. The number of log records is therefore -
48 backup records * 10 databases * 365 days * 5 years = 876000 records

If you find yourself in this situation, the following script will help.
It uses the nibble delete principle and cleans up backup history 1 day at a time, starting with the oldest record.

USE MSDB
GO
DECLARE @OldestBackupDate DATETIME
DECLARE @DaysToLeave INT
DECLARE @DaysToDeleteAtOnce INT
DECLARE @DeleteDate DATETIME

DECLARE @Counter INT
DECLARE @CounterText VARCHAR(30)

SELECT  @OldestBackupDate = MIN(backup_start_date) FROM msdb..backupset  
SELECT  @OldestBackupDate
SET @DaysToLeave = 30
SET @DaysToDeleteAtOnce = 1

SELECT @Counter = DATEDIFF(DAY,@OldestBackupDate,GETDATE())

WHILE @Counter >= @DaysToLeave  
BEGIN   
 SET @CounterText = CONVERT(VARCHAR(30),DATEADD(DAY, -@Counter,GETDATE()),21) 
 SELECT @DeleteDate = CONVERT(VARCHAR(30),DATEADD(DAY, -@Counter,GETDATE()),21) 
 RAISERROR (@CounterText , 10, 1) WITH NOWAIT   
 EXEC sp_delete_backuphistory @DeleteDate ;
 SELECT @Counter = @Counter - @DaysToDeleteAtOnce  
END 

It reports progress to screen, like this -

2006-06-17 16:27:59.970

Backup history older than Jun 17 2006 4:27PM has been deleted.

2006-06-18 16:28:42.067

Backup history older than Jun 18 2006 4:28PM has been deleted.

2006-06-19 16:30:08.853

Backup history older than Jun 19 2006 4:30PM has been deleted.

2006-06-20 16:31:32.653