Ultimate Delete Backup History Script! (nibble delete)

Following my musings on Backup History Performance, i.e. creating additional indexes in MSDB and nibble deleting those records I have found a further trick.

I've created solace_delete_backuphistory which manually deletes from the msdb tables, only with NOLOCK hints too. Much faster than sp_delete_backuphistory, and it is listed here along with the supporting indexes and loop...

-- Create Indexes
USE msdb
GO
CREATE INDEX NCI_backupset_backup_set_id ON backupset(backup_set_id)
GO
CREATE INDEX NCI_backupset_backup_set_uuid ON backupset(backup_set_uuid)
GO
CREATE INDEX NCI_backupset_media_set_id ON backupset(media_set_id)
GO
CREATE INDEX NCI_backupset_backup_finish_date ON backupset(backup_finish_date)
GO
CREATE INDEX NCI_backupset_backup_start_date ON backupset(backup_start_date)
GO
CREATE INDEX NCI_backupmediaset_media_set_id ON backupmediaset(media_set_id)
GO
CREATE INDEX NCI_backupfile_backup_set_id ON backupfile(backup_set_id)
GO
CREATE INDEX  NCI_backupmediafamily_media_set_id ON backupmediafamily(media_set_id)
GO
CREATE INDEX  NCI_restorehistory_restore_history_id ON restorehistory(restore_history_id)
GO
CREATE INDEX NCI_restorehistory_backup_set_id ON restorehistory(backup_set_id)
GO
CREATE INDEX  NCI_restorefile_restore_history_id ON restorefile(restore_history_id)
GO
CREATE INDEX NCI_restorefilegroup_restore_history_id ON restorefilegroup(restore_history_id)
GO

-- Custom delete backup history
USE MSDB
GO
CREATE PROC solace_delete_backuphistory (@cleardate DATETIME)
AS
BEGIN
SET NOCOUNT ON
DELETE FROM msdb..restorefile
FROM msdb..restorefile rf WITH (NOLOCK)
INNER JOIN msdb..restorehistory rh WITH (NOLOCK) ON rf.restore_history_id = rh.restore_history_id
INNER JOIN msdb..backupset bs WITH (NOLOCK) ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date <= @cleardate

DELETE FROM msdb..restorefilegroup
FROM msdb..restorefilegroup rfg WITH (NOLOCK) 
INNER JOIN msdb..restorehistory rh WITH (NOLOCK) ON rfg.restore_history_id = rh.restore_history_id
INNER JOIN msdb..backupset bs WITH (NOLOCK) ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date  <= @cleardate

DELETE FROM msdb..restorehistory
FROM msdb..restorehistory rh WITH (NOLOCK) 
INNER JOIN msdb..backupset bs WITH (NOLOCK) ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date  <= @cleardate

DELETE FROM msdb..backupfilegroup
FROM msdb..backupfilegroup bfg WITH (NOLOCK) 
INNER JOIN msdb..backupset bs WITH (NOLOCK) ON bfg.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date  <= @cleardate


DELETE FROM msdb..backupfile
FROM msdb..backupfile bf WITH (NOLOCK) 
INNER JOIN msdb..backupset bs WITH (NOLOCK) ON bf.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date  <= @cleardate

DELETE FROM msdb..backupset 
WHERE backup_finish_date  <= @cleardate
SET NOCOUNT OFF
END
GO
 
-- Nibble Delete Backup History with loop
USE MSDB
GO
DECLARE @OldestBackupDate DATETIME
DECLARE @DaysToLeave INT
DECLARE @DaysToDeleteAtOnce INT
DECLARE @DeleteDate DATETIME

DECLARE @Counter INT
DECLARE @CounterText VARCHAR(100)

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),GETDATE(),21) + ' processing ' + 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 ;
 EXEC solace_delete_backuphistory @cleardate = @DeleteDate
 SELECT @Counter = @Counter - @DaysToDeleteAtOnce  
END 

GO