Quick Scan Report – Excessive Backup History

What this check looks for

msdb.dbo.backupset holding more than a year of data or more than 100,000 rows. Either condition raises the finding.

Why it matters

The row count is the part that matters here, and it is not the same as the age.

An instance with 40 databases taking log backups every 15 minutes generates about 1.4 million backupset rows a year. An instance with three databases and nightly fulls generates about a thousand. Both may be “a year of history”, and only one of them is a problem. That is why this check has a row count threshold as well as a date threshold, and the row count is the one that predicts trouble.

What slows down as the row count grows:

• The Management Studio restore dialog. It queries the backup history to build the list of available backups, and on a table with millions of rows it can take minutes or appear to hang. This is the symptom people actually notice, and they notice it during a restore.
• Every backup operation, because each one inserts into these tables, and the inserts get slower as the indexes grow.
• Every restore, for the same reason, plus the lookup.
• msdb maintenance, including its own backups and integrity checks.
• Any monitoring that reads backup history, which is most backup monitoring.

And the purge itself becomes the problem. sp_delete_backuphistory deletes from about eight related tables, and the default indexing on those tables does not support the delete pattern well. On a table with millions of rows, the purge can run for hours and block backups while it runs. The first purge is the dangerous one, and it gets more dangerous the longer it is deferred.

So the practical shape of this finding is: it is a small housekeeping item today and a several hour blocking operation in two years. Fixing it now is cheap.

The related trap: msdb itself is frequently left in default configuration, small initial size with percentage growth, on the C: drive. A backupset table growing to several gigabytes then produces file growth events, VLF proliferation in the msdb log, and in the worst case a full system drive.

How to confirm it yourself

The two thresholds this check uses:

SELECT MIN([backup_start_date]) AS [first_backup],
       MAX([backup_start_date]) AS [last_backup],
       COUNT(*)                 AS [total_rows],
       DATEDIFF(DAY, MIN([backup_start_date]), GETDATE()) AS [days_of_history]
  FROM msdb.dbo.backupset WITH (NOLOCK);

Where the rows come from, which tells you the growth rate:

SELECT [type],
       CASE [type] WHEN 'D' THEN 'Full'
                   WHEN 'I' THEN 'Differential'
                   WHEN 'L' THEN 'Log'
                   WHEN 'F' THEN 'File or filegroup'
                   ELSE [type] END AS [backup_type],
       COUNT(*)                    AS [rows]
  FROM msdb.dbo.backupset WITH (NOLOCK)
 GROUP BY [type]
 ORDER BY [rows] DESC;

Log backups usually account for the overwhelming majority, which is expected and is the reason the row count grows so much faster than intuition suggests.

Rows added per day, which projects forward:

SELECT CAST([backup_start_date] AS DATE) AS [day], COUNT(*) AS [rows]
  FROM msdb.dbo.backupset WITH (NOLOCK)
 WHERE [backup_start_date] > DATEADD(DAY, -14, GETDATE())
 GROUP BY CAST([backup_start_date] AS DATE)
 ORDER BY [day] DESC;

The size across all the related tables:

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]) LIKE 'backup%'
    OR OBJECT_NAME(p.[object_id]) LIKE 'restore%'
   AND p.[index_id] IN (0, 1)
 GROUP BY p.[object_id]
 ORDER BY [size_mb] DESC;

And whether msdb itself is configured sensibly, since it is about to be asked to hold all of this:

SELECT mf.[name]                                     AS [logical_name],
       mf.[type_desc],
       CAST(mf.[size] * 8.0 / 1024 AS DECIMAL(12,1)) AS [size_mb],
       CASE WHEN mf.[is_percent_growth] = 1 THEN CAST(mf.[growth] AS VARCHAR(10)) + ' %'
            ELSE CAST(mf.[growth] * 8 / 1024 AS VARCHAR(10)) + ' MB' END AS [growth],
       mf.[physical_name]
  FROM sys.master_files AS mf WITH (NOLOCK)
 WHERE mf.[database_id] = DB_ID('msdb');

How to fix it

Add the indexes, purge in increments, then schedule it. The same three steps as the age based finding, and the row count is why the increments matter.

1. Index the history tables, which turns a multi hour purge into a manageable one:

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_restorehistory_backup_set_id]
    ON [dbo].[restorehistory] ([backup_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]);

2. Size msdb properly before the purge, because the delete generates log:

ALTER DATABASE [msdb] MODIFY FILE (NAME = N'MSDBData', SIZE = 2GB,  FILEGROWTH = 256MB);
ALTER DATABASE [msdb] MODIFY FILE (NAME = N'MSDBLog',  SIZE = 1GB,  FILEGROWTH = 256MB);

3. Purge in small steps, weekly rather than monthly if the row count is in the millions:

DECLARE @cutoff DATETIME = (SELECT MIN([backup_start_date]) FROM msdb.dbo.backupset);
DECLARE @target DATETIME = DATEADD(DAY, -90, GETDATE());

WHILE @cutoff < @target
BEGIN
    SET @cutoff = DATEADD(WEEK, 1, @cutoff);
    IF @cutoff > @target SET @cutoff = @target;

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

    -- let backups through between batches
    WAITFOR DELAY '00:00:05';
END;

Run it outside the backup window and watch for blocking. If backups start queueing, stop and resume later; the work already done is committed.

4. Schedule the ongoing purge so it never rebuilds:

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

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_jobschedule
     @job_name = N'DBA - Purge msdb History',
     @name = N'Weekly', @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';

5. Rebuild the indexes and reclaim space afterwards:

USE [msdb];
GO
ALTER INDEX ALL ON [dbo].[backupset] REBUILD;
ALTER INDEX ALL ON [dbo].[backupfile] REBUILD;
DBCC SHRINKFILE (N'MSDBData', 0, TRUNCATEONLY);

And check the restore dialog afterwards, because that is where the improvement is most visible and it is the thing you will be using under pressure.

How long it takes

About an hour to index and schedule. The initial purge on a table with millions of rows can run for hours and should be done in batches outside the backup window.

Related reports

Related checks

Frequently asked questions

Why is the row count a separate threshold from the age? Because an instance with frequent log backups on many databases generates a million rows a year, and one with a few nightly fulls generates a thousand. The row count predicts the slowdown; the age does not.

My restore dialog takes minutes to open. That is the usual symptom. The dialog queries this history, and the row count is why. Purging fixes it.

Is it safe to delete history for backups I still have? Yes. The history is a record, not the backup. You can still restore a file by specifying it directly.

The purge is blocking my backups. Stop it and run it in smaller batches outside the backup window. Adding the indexes first makes each batch far faster.