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
