Quick Scan Report – Extended Backup History

What this check looks for

Rows in msdb.dbo.backupset with a backup date more than a year old. The message reports how many.

Why it matters

SQL Server records every backup and every restore in msdb permanently. There is no automatic cleanup, so on an instance that has never been purged, the history goes back to the day it was built.

The first question to ask is what the old rows are actually for. A row in backupset describes a backup file: when it was taken, how large it was, its LSNs, and where it was written. That is useful for as long as the file exists. Once the backup file has been deleted by your retention policy, the row describes something that is gone. Keeping a year of rows for backups you retain for 30 days means eleven months of records pointing at nothing.

What the accumulation costs:

• msdb grows. The history is spread over about a dozen tables, and on a busy instance with frequent log backups it can reach several gigabytes.
• Restore dialogs become slow. The Management Studio restore wizard queries this history, and on a large backupset it can take minutes to populate, or appear to hang.
• Backup and restore operations themselves slow down, because each one inserts into these tables and the inserts get slower as the tables grow.
• Maintenance on msdb takes longer, including its own backups and integrity checks.
• Reporting over backup history gets slower, which affects the monitoring that reads it.

And there is a well known indexing problem underneath. The default indexes on the backup history tables are not adequate for the delete pattern that sp_delete_backuphistory uses. On a table that has grown very large, the purge itself can run for hours and block backups while it does. That is the trap in this finding: the longer you leave it, the harder the cleanup becomes, and the first purge on a table that has never been purged is the one that causes an incident.

So the order of operations matters. Add the supporting indexes first, purge in small increments, and only then set up the ongoing schedule.

How much to keep is a judgment, and the useful framing is: slightly longer than your backup retention, plus whatever a compliance requirement demands. If backups are kept for 35 days, keeping 90 days of history is generous. Keeping five years is keeping a record of files that were deleted in 2021.

How to confirm it yourself

How much history there is:

SELECT MIN([backup_start_date]) AS [oldest_backup],
       MAX([backup_start_date]) AS [newest_backup],
       COUNT(*)                 AS [total_rows],
       COUNT(DISTINCT [database_name]) AS [databases]
  FROM msdb.dbo.backupset WITH (NOLOCK);

By year, which shows the shape of the problem:

SELECT YEAR([backup_start_date]) AS [year],
       COUNT(*)                  AS [rows]
  FROM msdb.dbo.backupset WITH (NOLOCK)
 GROUP BY YEAR([backup_start_date])
 ORDER BY [year];

What it is costing in msdb:

USE [msdb];
GO
SELECT OBJECT_NAME(p.[object_id])                     AS [table_name],
       SUM(p.[rows])                                  AS [rows],
       CAST(SUM(a.[total_pages]) * 8.0 / 1024 AS DECIMAL(12,1)) AS [size_mb]
  FROM sys.partitions        AS p WITH (NOLOCK)
 INNER JOIN sys.allocation_units AS a WITH (NOLOCK) ON a.[container_id] = p.[partition_id]
 WHERE OBJECT_NAME(p.[object_id]) IN
       ('backupset', 'backupfile', 'backupfilegroup', 'backupmediafamily',
        'backupmediaset', 'restorehistory', 'restorefile', 'restorefilegroup')
   AND p.[index_id] IN (0, 1)
 GROUP BY p.[object_id]
 ORDER BY [size_mb] DESC;

And the size of msdb overall:

SELECT [name],
       CAST([size] * 8.0 / 1024 AS DECIMAL(12,1)) AS [size_mb],
       [type_desc]
  FROM sys.master_files WITH (NOLOCK)
 WHERE [database_id] = DB_ID('msdb');

Whether anything is already purging it:

SELECT j.[name] AS [job_name], st.[step_name], st.[command]
  FROM msdb.dbo.sysjobsteps AS st
 INNER JOIN msdb.dbo.sysjobs AS j ON j.[job_id] = st.[job_id]
 WHERE st.[command] LIKE '%sp_delete_backuphistory%'
    OR st.[command] LIKE '%sp_purge_jobhistory%';

An empty result on an instance with years of history is the whole explanation.

How to fix it

Index first, purge in increments, then schedule. Doing it in that order avoids the long blocking delete.

1. Add the supporting indexes. These are widely recommended and make the purge orders of magnitude faster:

USE [msdb];
GO

CREATE NONCLUSTERED INDEX [IX_backupset_backup_set_id]
    ON [dbo].[backupset] ([backup_set_id]);

CREATE NONCLUSTERED INDEX [IX_backupset_media_set_id]
    ON [dbo].[backupset] ([media_set_id]);

CREATE NONCLUSTERED INDEX [IX_backupfile_backup_set_id]
    ON [dbo].[backupfile] ([backup_set_id]);

CREATE NONCLUSTERED INDEX [IX_backupfilegroup_backup_set_id]
    ON [dbo].[backupfilegroup] ([backup_set_id]);

CREATE NONCLUSTERED INDEX [IX_backupmediafamily_media_set_id]
    ON [dbo].[backupmediafamily] ([media_set_id]);

CREATE NONCLUSTERED INDEX [IX_restorefile_restore_history_id]
    ON [dbo].[restorefile] ([restore_history_id]);

CREATE NONCLUSTERED INDEX [IX_restorefilegroup_restore_history_id]
    ON [dbo].[restorefilegroup] ([restore_history_id]);

CREATE NONCLUSTERED INDEX [IX_restorehistory_backup_set_id]
    ON [dbo].[restorehistory] ([backup_set_id]);

2. Purge in increments, oldest first. Do not run a single delete covering years:

-- work forward a month at a time
DECLARE @cutoff DATETIME = '2019-01-01';

WHILE @cutoff < DATEADD(DAY, -90, GETDATE())
BEGIN
    EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @cutoff;
    SET @cutoff = DATEADD(MONTH, 1, @cutoff);
END;

Run this outside the backup window, and watch for blocking. The first few months on a very large table are the slow ones; after that it accelerates.

3. Schedule the ongoing purge, which is what stops this recurring:

EXEC msdb.dbo.sp_add_job
     @job_name = N'DBA - Purge msdb History',
     @description = N'Keeps 90 days of backup and job history in msdb.';

EXEC msdb.dbo.sp_add_jobstep
     @job_name      = N'DBA - Purge msdb History',
     @step_name     = N'Purge backup history',
     @subsystem     = N'TSQL',
     @database_name = N'msdb',
     @command       = N'DECLARE @d DATETIME = DATEADD(DAY, -90, GETDATE());
                        EXEC sp_delete_backuphistory @oldest_date = @d;';

EXEC msdb.dbo.sp_add_jobstep
     @job_name      = N'DBA - Purge msdb History',
     @step_name     = N'Purge job history',
     @subsystem     = N'TSQL',
     @database_name = N'msdb',
     @command       = N'DECLARE @d DATETIME = DATEADD(DAY, -90, GETDATE());
                        EXEC sp_purge_jobhistory @oldest_date = @d;';

EXEC msdb.dbo.sp_add_jobschedule
     @job_name = N'DBA - Purge msdb History',
     @name = N'Weekly Sunday', @freq_type = 8, @freq_interval = 1,
     @freq_recurrence_factor = 1, @active_start_time = 030000;

EXEC msdb.dbo.sp_add_jobserver @job_name = N'DBA - Purge msdb History';

4. Reclaim the space once the purge has finished, if msdb has grown substantially:

USE [msdb];
GO
DBCC SHRINKFILE (N'MSDBData', 0, TRUNCATEONLY);

TRUNCATEONLY releases free space at the end of the file without moving pages, which avoids the fragmentation a full shrink causes.

5. Rebuild the indexes on those tables after a large purge, since the deletes will have left them fragmented.

How long to keep: 90 days suits most instances. Keep longer only if a compliance requirement says so, and remember that the rows describe backup files, so history much older than your file retention describes nothing you can restore.

How long it takes

About an hour to add the indexes and set up the job. The first purge on a very large table can run for hours, which is why it is done in increments outside the backup window.

Related reports

Related checks

Frequently asked questions

Will purging affect my ability to restore? No. The history is a record, not the backups. Restoring from a file whose history row has been purged works normally; you specify the file rather than picking it from a list.

How much history should I keep? Slightly longer than your backup file retention, typically 90 days, plus whatever a compliance requirement adds. Rows describing deleted files serve no purpose.

The purge has been running for hours. Expected on a table that has never been purged, and the reason to add the indexes first and work in monthly increments. Stop it if it is blocking backups and resume outside the window.

Does this need downtime? No, but run it outside the backup window. The purge takes locks on the tables that backups insert into.