Removing large backup history with sp_delete_backuphistory

Problem

In SQL Server, the sp_delete_backuphistory stored procedure is is the standard and supported method to purge old data from the backup and restore history tables in the msdb database:

backupfile
backupfilegroup
backupmediafamily
backupmediaset
backupset
restorefile
restorefilegroup
restorehistory

Recently, I encountered an issue running sp_delete_backuphistory on servers that hosted a large number of databases with frequent log backup & restore operations. The clean up task hadn’t been scheduled and the history tables had grown very large over several months. The msdb databases was also hosted on a volume with limited IOPs.

Attempting to run sp_delete_backuphistory under these conditions you will likely encounter these issues:

The operation will take a long time
Log backup and restore operations are blocked during the process
Your msdb log file will grow large if you try to remove too much data at once
If you kill off the process at any point, the whole thing will rollback.

The sp_delete_backuphistory stored procedure gets the job done in most cases but it’s not very well optimized if you have a large amount of history to clear.

Optimized Solution for Backup History Cleanup

To clear a large amount of history without resorting to schema modifications or unsupported methods we can take an incremental approach to removing data.

The script below will check how much retention we currently have. It will then call sp_delete_backuphistory in a loop, removing data 1hr (configurable) at a time until we hit the target retention. We can also specify a maximum runtime for the process.

This approach won’t necessarily speed things up but it provides the following benefits:

Data is cleared as a series of smaller transactions rather than a single large transaction
Smaller transactions allow for log truncation and prevent the log file from growing.
We can interrupt the process without having to rollback all the progress that has been made. (just the current batch)
Log backups/restores might still be blocked, but only for the duration it takes to complete a single loop – not for the entire time it takes to clear the history. This can still impact RPO/RTO though.
We can control how long the process runs for. We can keep it running outside business hours or allow for breaks to prevent log backup/restores from falling too far behind.
The script will print progress information.

/********************* Params *******************/
DECLARE @RetentionDays INT = 30 /* How long should we keep the data for in days */
DECLARE @Mins INT = 60 /* Delete data X minutes at a time.  Small interval keeps transaction short.  Less blocking and log usage*/
DECLARE @MaxRuntimeMins INT = 60 /* Stop processing if runtime exceeds this threshold */
/************************************************/

DECLARE @TargetDate DATETIME
DECLARE @oldest_date DATETIME
DECLARE @Message NVARCHAR(4000)
DECLARE @t1 DATETIME
DECLARE @Duration INT
DECLARE @TotalDuration BIGINT=0
DECLARE @IterationCount INT=0
DECLARE @StopAt DATETIME

/* The target date we want to get to based on the specified @RetentionDays */
SET @TargetDate = DATEADD(dd,-@RetentionDays,GETDATE())
/* When should we stop processing batches based on @MaxRuntimeMins */
SET @StopAt = DATEADD(mi,@MaxRuntimeMins,GETDATE())

/* Get the oldest backup finish date from table.  We will start removing data older than this point + @Mins minutes */
SELECT @oldest_date =  MIN(backup_finish_date)
FROM msdb.dbo.backupset

PRINT CONCAT('Oldest Date: ',@oldest_date)
PRINT CONCAT('Target Date: ', @TargetDate)
PRINT CONCAT('Max Run Time (Mins): ',@MaxRuntimeMins,', Processing will stop after ',@StopAt)

/* Process the delete operation in small chunks until we reach the target date for our retention */
 WHILE 1=1
 BEGIN
	/* Increment date by @Mins minutes */
	SET @oldest_date = DATEADD(mi,@Mins,@oldest_date)
	/* Ensure that we don't overshoot our target date */
	IF @oldest_date > @TargetDate
		SET @oldest_date = @TargetDate	
		
	SET @IterationCount = @IterationCount + 1
	/* 
	Print message using RAISERROR WITH NOWAIT so message is printed immediately.  
	For keeping track of progress 
	*/
	SET @Message = CONCAT(CONVERT(VARCHAR,GETDATE(),114),' Start iteration ',@IterationCount,': Removing backup history before ',@oldest_date)
	RAISERROR(@Message,0,1) WITH NOWAIT
	SET @t1 = SYSUTCDATETIME()
	/* Remove the history up to @oldest_date (@Mins minutes worth of history) */
	EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @oldest_date;

	/* Print how long this iteration took &amp; keep track of total duration and iteration count */
	SET @Duration = DATEDIFF(ms,@t1,SYSUTCDATETIME())
	SET @TotalDuration = @TotalDuration +  @Duration 
	SET @Message = CONCAT(CONVERT(VARCHAR,GETDATE(),114),' Iteration ', @IterationCount,' completed in (hh:mi:ss:mmm): ',CONVERT(VARCHAR,DATEADD(ms,@Duration,0),114))
	
	RAISERROR(@Message,0,1) WITH NOWAIT

	/* Stop once we hit our target date for retention or we have ran for longer than @MaxRuntimeMins */
	IF @oldest_date>=@TargetDate 
		OR GETDATE() > @StopAt
		BREAK

 END

 /* 
	Print overall stats 
	Accounting for possibility that total duration could span > 1 day
 */
SET @Message = CONCAT('Completed ',
				@IterationCount,
				' iterations in ',
				CAST(NULLIF(DATEDIFF(d,0,DATEADD(s,@TotalDuration/1000,0)),0) AS NVARCHAR(4000)) + ' Days ',
				LEFT(CONVERT(VARCHAR,DATEADD(s,@TotalDuration/1000,0),114),8),
				' (hh:mi:ss), Avg Duration: '
				,CONVERT(VARCHAR,DATEADD(ms,@TotalDuration/NULLIF(@IterationCount,0),0),114), 
				' (hh:mi:ss:mmm). Reached target date: ',CASE WHEN @oldest_date>=@TargetDate THEN 'YES' ELSE 'NO' END )

RAISERROR(@Message,0,1) WITH NOWAIT

/********************* Params *******************/

DECLARE @RetentionDays INT = 30 /* How long should we keep the data for in days */

DECLARE @Mins INT = 60 /* Delete data X minutes at a time. Small interval keeps transaction short. Less blocking and log usage*/

DECLARE @MaxRuntimeMins INT = 60 /* Stop processing if runtime exceeds this threshold */

/************************************************/

DECLARE @TargetDate DATETIME

DECLARE @oldest_date DATETIME

DECLARE @Message NVARCHAR(4000)

DECLARE @t1 DATETIME

DECLARE @Duration INT

DECLARE @TotalDuration BIGINT=0

DECLARE @IterationCount INT=0

DECLARE @StopAt DATETIME

/* The target date we want to get to based on the specified @RetentionDays */

SET @TargetDate = DATEADD(dd,-@RetentionDays,GETDATE())

/* When should we stop processing batches based on @MaxRuntimeMins */

SET @StopAt = DATEADD(mi,@MaxRuntimeMins,GETDATE())

/* Get the oldest backup finish date from table. We will start removing data older than this point + @Mins minutes */

SELECT @oldest_date = MIN(backup_finish_date)

FROM msdb.dbo.backupset

PRINT CONCAT('Oldest Date: ',@oldest_date)

PRINT CONCAT('Target Date: ', @TargetDate)

PRINT CONCAT('Max Run Time (Mins): ',@MaxRuntimeMins,', Processing will stop after ',@StopAt)

/* Process the delete operation in small chunks until we reach the target date for our retention */

WHILE 1=1

BEGIN

/* Increment date by @Mins minutes */

SET @oldest_date = DATEADD(mi,@Mins,@oldest_date)

/* Ensure that we don't overshoot our target date */

IF @oldest_date > @TargetDate

SET @oldest_date = @TargetDate

SET @IterationCount = @IterationCount + 1

Print message using RAISERROR WITH NOWAIT so message is printed immediately.

For keeping track of progress

SET @Message = CONCAT(CONVERT(VARCHAR,GETDATE(),114),' Start iteration ',@IterationCount,': Removing backup history before ',@oldest_date)

RAISERROR(@Message,0,1) WITH NOWAIT

SET @t1 = SYSUTCDATETIME()

/* Remove the history up to @oldest_date (@Mins minutes worth of history) */

EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @oldest_date;

/* Print how long this iteration took & keep track of total duration and iteration count */

SET @Duration = DATEDIFF(ms,@t1,SYSUTCDATETIME())

SET @TotalDuration = @TotalDuration + @Duration

SET @Message = CONCAT(CONVERT(VARCHAR,GETDATE(),114),' Iteration ', @IterationCount,' completed in (hh:mi:ss:mmm): ',CONVERT(VARCHAR,DATEADD(ms,@Duration,0),114))

RAISERROR(@Message,0,1) WITH NOWAIT

/* Stop once we hit our target date for retention or we have ran for longer than @MaxRuntimeMins */

IF @oldest_date>=@TargetDate

OR GETDATE() > @StopAt

BREAK

END

Print overall stats

Accounting for possibility that total duration could span > 1 day

SET @Message = CONCAT('Completed ',

@IterationCount,

' iterations in ',

CAST(NULLIF(DATEDIFF(d,0,DATEADD(s,@TotalDuration/1000,0)),0) AS NVARCHAR(4000)) + ' Days ',

LEFT(CONVERT(VARCHAR,DATEADD(s,@TotalDuration/1000,0),114),8),

' (hh:mi:ss), Avg Duration: '

,CONVERT(VARCHAR,DATEADD(ms,@TotalDuration/NULLIF(@IterationCount,0),0),114),

' (hh:mi:ss:mmm). Reached target date: ',CASE WHEN @oldest_date>=@TargetDate THEN 'YES' ELSE 'NO' END )

RAISERROR(@Message,0,1) WITH NOWAIT

Links:

These related links might be interesting:

https://www.brentozar.com/archive/2018/02/the-annals-of-hilariously-bad-code-part-1/
https://www.brentozar.com/archive/2018/02/annals-hilariously-bad-code-part-2/
https://dba.stackexchange.com/questions/233375/how-to-minimize-plan-ahead-the-effect-of-running-sp-delete-backuphistory-measur

Problem

Optimized Solution for Backup History Cleanup

Links:

Share this:

Like this: