Uncategorized

Delete large backup history

31. 1. 2026

msdb can get massive when the backup history isn’t purged regularly. In that state, sp_delete_backuphistory can run for a very long time (and sometimes becomes a maintenance nightmare).

I built an alternative cleanup script that:

  • purges old rows in batches (TOP + loop)
  • is designed to be restartable and predictable
  • optionally adds WAITFOR to reduce pressure
USE [msdb]
GO

SET NOCOUNT ON

DECLARE @BackupDate DATE
DECLARE @Msg NVARCHAR(MAX)

DROP TABLE IF EXISTS [#backup_set_id]
DROP TABLE IF EXISTS [#media_set_id]
DROP TABLE IF EXISTS [#restore_history_id]

CREATE TABLE [#backup_set_id] ([backup_set_id] INT NOT NULL PRIMARY KEY)
CREATE TABLE [#media_set_id] ([media_set_id] INT NOT NULL PRIMARY KEY)
CREATE TABLE [#restore_history_id] ([restore_history_id] INT NOT NULL PRIMARY KEY)

DROP TABLE IF EXISTS [#Dates]

CREATE TABLE [#Dates] ( [BackupDate] DATE NOT NULL PRIMARY KEY)


INSERT INTO [#Dates] 
	(	[BackupDate] )
	SELECT 
		DISTINCT CAST([backup_finish_date] AS DATE)
	FROM [dbo].[backupset]
	WHERE [backup_finish_date] < DATEADD(dd,-5,GETDATE())

WHILE EXISTS (SELECT * FROM [#Dates])
BEGIN

	SELECT TOP(1) 
		@BackupDate = [BackupDate]		
	FROM [#Dates]
	ORDER BY [BackupDate]

	SET @Msg = CAST(@BackupDate AS NVARCHAR(20))
	RAISERROR('%s', 10, 1, @Msg) WITH NOWAIT

	--EXECUTE [dbo].[sp_delete_backuphistory] @BackupDate
    
    BEGIN TRY

        TRUNCATE TABLE [#backup_set_id]
        TRUNCATE TABLE [#media_set_id]
        TRUNCATE TABLE [#restore_history_id]

	    INSERT INTO [#backup_set_id] ([backup_set_id])
            SELECT DISTINCT [backup_set_id]
            FROM [msdb].[dbo].[backupset]
            WHERE [backup_finish_date] < @BackupDate

        INSERT INTO [#media_set_id] ([media_set_id])
            SELECT DISTINCT [media_set_id]
            FROM [msdb].[dbo].[backupset]
            WHERE [backup_finish_date] < @BackupDate

        INSERT INTO [#restore_history_id] ([restore_history_id])
            SELECT DISTINCT [restore_history_id]
            FROM [msdb].[dbo].[restorehistory]
            WHERE [backup_set_id] IN (SELECT [backup_set_id] FROM [#backup_set_id])

        BEGIN TRANSACTION

            DELETE FROM [msdb].[dbo].[backupfile]
            WHERE [backup_set_id] IN (SELECT [backup_set_id] FROM [#backup_set_id])

            DELETE FROM [msdb].[dbo].[backupfilegroup]
            WHERE [backup_set_id] IN (SELECT [backup_set_id] FROM [#backup_set_id])
            
            DELETE FROM [msdb].[dbo].[restorefile]
            WHERE [restore_history_id] IN (SELECT [restore_history_id] FROM [#restore_history_id])
            
            DELETE FROM [msdb].[dbo].[restorefilegroup]
            WHERE [restore_history_id] IN (SELECT [restore_history_id] FROM [#restore_history_id])
            
            DELETE FROM [msdb].[dbo].[restorehistory]
            WHERE [restore_history_id] IN (SELECT [restore_history_id] FROM [#restore_history_id])

            DELETE FROM [msdb].[dbo].[backupset]
            WHERE [backup_set_id] IN (SELECT [backup_set_id] FROM [#backup_set_id])
            
            DELETE [msdb].[dbo].[backupmediafamily]
            FROM [msdb].[dbo].[backupmediafamily] [bmf]
            WHERE [bmf].[media_set_id] IN (SELECT [media_set_id] FROM [#media_set_id])
                  AND ((SELECT COUNT(*) FROM [msdb].[dbo].[backupset] WHERE [media_set_id] = [bmf].[media_set_id]) = 0)

            DELETE [msdb].[dbo].[backupmediaset]
            FROM [msdb].[dbo].[backupmediaset] [bms]
            WHERE [bms].[media_set_id] IN (SELECT [media_set_id] FROM [#media_set_id])
                  AND ((SELECT COUNT(*) FROM [msdb].[dbo].[backupset] WHERE [media_set_id] = [bms].[media_set_id]) = 0)

        COMMIT TRANSACTION

    END TRY
    BEGIN CATCH

        IF @@TRANCOUNT > 0
            ROLLBACK

        ;THROW

    END CATCH
    
	DELETE FROM [#Dates] WHERE [BackupDate] = @BackupDate

END
GO

 

4
Share: