This week I noticed some slowness on queries related to backup history. The instance had a huge msdb database (more than 10GB size), the backup history had never been purged.

I already wrote a post on this topic and provided a small PowerShell script using dbatools to clean the backup history.
The issue with sp_delete_backuphistory or Remove-DbaDbBackupRestoreHistory is that the operation is done as one transaction and this could badly impact the msdb transaction log file.

Remember you can have a look at the code behind a system stored procedure using sp_helptext.

1
2
3
use msdb
go
exec sp_helptext sp_delete_backuphistory

The code for sp_delete_backuphistory looks like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
CREATE   PROCEDURE sp_delete_backuphistory 
  @oldest_date datetime 
AS
BEGIN
  SET NOCOUNT ON
  
  DECLARE @backup_set_id TABLE      (backup_set_id INT
  DECLARE @media_set_id TABLE       (media_set_id INT
  DECLARE @restore_history_id TABLE (restore_history_id INT
  
  INSERT INTO @backup_set_id (backup_set_id) 
  SELECT DISTINCT backup_set_id [...]
  
  INSERT INTO @media_set_id (media_set_id) 
  SELECT DISTINCT media_set_id [...]
  
  INSERT INTO @restore_history_id (restore_history_id) 
  SELECT DISTINCT restore_history_id [...] 
  
  BEGIN TRANSACTION
  
  DELETE FROM msdb.dbo.backupfile 
  WHERE backup_set_id IN (SELECT backup_set_id 
                          FROM @backup_set_id) 
  IF (@@error > 0) 
    GOTO Quit 
  
  DELETE FROM msdb.dbo.backupfilegroup 
  WHERE backup_set_id IN (SELECT backup_set_id 
                          FROM @backup_set_id) 
  IF (@@error > 0) 
    GOTO Quit 
  
  [...]

In order to clean up the backup history while minimizing the impact on the size of the transaction log file, the operation should be done in small chunks.

Here is the small T-SQL script that will do the job:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
use msdb
go
declare @retentionDate datetime = DATEADD(MONTH, -3, getdate())
declare @oldest_date datetime = (select min(backup_start_date) from msdb.dbo.backupset)
while (@oldest_date  < @retentionDate)
begin
    print 'sp_delete_backuphistory ' + CAST(@oldest_date AS varchar)
    exec msdb.dbo.sp_delete_backuphistory @oldest_date
    --  Delete by 1 week increments
    set @oldest_date = DATEADD(WEEK, 1, @oldest_date)
    checkpoint
end
exec msdb.dbo.sp_delete_backuphistory @retentionDate
go

The sp_delete_backuphistory procedure is called in 1-week increments from the oldest entry in msdb.dbo.backupset until the retention date which I set to 3 months.
An important point here is the checkpoint command which actually performs a manual checkpoint and helps to keep the impact on the transaction log size at a minimum when the database is in the SIMPLE recovery model. You can read more about this technic in this great writing from Aaron Bertrand.

If you don’t already have one, I would recommend creating a Job on your SQL Server instance that will call sp_delete_backuphistory weekly or fortnightly. This can be a standard Job on all your servers.

Here is the script for such a Job with its schedule. The retention is set to 6 months of backup history.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT = 0;
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA_MAINTENANCE_BACKUP_HISTORY',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'Purge backup history',
        @category_name=N'Database Maintenance',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DBA_MAINTENANCE_BACKUP_HISTORY_STEP',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'declare @retentionDate datetime = DATEADD(MONTH, -6, getdate());
exec msdb.dbo.sp_delete_backuphistory @retentionDate;',
        @database_name=N'msdb',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'WeeklySchedule_Sunday_10pm',
        @enabled=1,
        @freq_type=8,
        @freq_interval=1,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20210422,
        @active_end_date=99991231,
        @active_start_time=220000,
        @active_end_time=235959,
        @schedule_uid=N'f41b6a99-3d5d-49d7-871b-5e8a9ac2c0eb'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO