Quick Scan Report – Excessive Backup History

Proper care and feeding of the msdb.dbo.backupset tables in SQL Server is critical for optimal performance.

Safely Clearing msdb.dbo.backupset in SQL Server

The msdb.dbo.backupset table in SQL Server stores details about backup operations, including backup start time, type, duration, and more. Over time, especially in environments with frequent backups, this table can grow significantly, potentially impacting performance and storage.

However, clearing data from msdb.dbo.backupset requires careful consideration to avoid unintended consequences, like losing historical backup metadata that could be critical for compliance or troubleshooting.

In this article, we’ll walk through the steps to clear data from msdb.dbo.backupset safely.

Why Clear backupset?

Common Reasons:

  1. Performance: A bloated msdb database can slow down queries against backup history.
  2. Storage: Large msdb databases can consume valuable disk space.
  3. Maintenance: Ensuring the msdb database remains manageable and efficient.

Precautions Before Clearing msdb.dbo.backupset

  1. Back Up msdb: Always create a full backup of msdb before making any changes: BACKUP DATABASE msdb TO DISK = 'C:\Backups\msdb_backup.bak';
  2. Understand Compliance Requirements: Verify if there are regulatory or organizational requirements to retain backup history for a specific period.
  3. Verify Backup Metadata: Ensure the data to be cleared is no longer needed. Run a query to preview the data: SELECT * FROM msdb.dbo.backupset WHERE backup_finish_date < DATEADD(MONTH, -6, GETDATE());

Steps to Clear backupset Safely

1. Determine the Retention Period

Decide how far back you need to retain backup history. For example, if you want to keep the last 6 months of data, use a query like this:

DELETE FROM msdb.dbo.backupset 
WHERE backup_finish_date < DATEADD(MONTH, -6, GETDATE());

2. Clear Related Tables

The backupset table is linked to msdb.dbo.backupmediafamily. Deleting records from backupset without addressing related data can lead to orphaned records.

Example:

DELETE FROM msdb.dbo.backupmediafamily
WHERE media_set_id NOT IN (SELECT media_set_id FROM msdb.dbo.backupset);

3. Run in Batches

For large datasets, deleting records in smaller batches minimizes transaction log growth and reduces locking:

WHILE EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE backup_finish_date < DATEADD(MONTH, -6, GETDATE()))
BEGIN
    DELETE TOP (1000) 
    FROM msdb.dbo.backupset 
    WHERE backup_finish_date < DATEADD(MONTH, -6, GETDATE());
    
    DELETE TOP (1000) 
    FROM msdb.dbo.backupmediafamily
    WHERE media_set_id NOT IN (SELECT media_set_id FROM msdb.dbo.backupset);
END

4. Rebuild and Reorganize Indexes

After significant deletions, rebuild or reorganize indexes on msdb.dbo.backupset to improve performance:

ALTER INDEX ALL ON msdb.dbo.backupset REBUILD;
ALTER INDEX ALL ON msdb.dbo.backupmediafamily REBUILD;

5. Monitor msdb Database Size

Verify the changes by checking the size of msdb:

EXEC sp_spaceused @objname = 'msdb.dbo.backupset';

Automating Cleanup with Maintenance Jobs

You can automate the process of clearing old backup history by creating a SQL Server Agent job:

  1. Create a Stored Procedure: CREATE PROCEDURE dbo.CleanupBackupHistory AS BEGIN DELETE FROM msdb.dbo.backupmediafamily WHERE media_set_id NOT IN (SELECT media_set_id FROM msdb.dbo.backupset); DELETE FROM msdb.dbo.backupset WHERE backup_finish_date < DATEADD(MONTH, -6, GETDATE()); END
  2. Schedule the Job: Use SQL Server Agent to run the procedure regularly, ensuring msdb remains clean and optimized.

Important Considerations

  1. Audit Trails: Before deletion, export data to a file for audit purposes if required.
  2. Avoid Immediate Impact: If other systems rely on backup history, verify those dependencies before clearing data.
  3. Testing: Always test the process on a non-production environment to avoid accidental data loss.

Conclusion

Managing the size of msdb.dbo.backupset is essential for maintaining SQL Server performance and storage efficiency. Following the steps outlined here ensures you can safely clean up outdated backup metadata without compromising critical data or system functionality.

If you’re managing a complex SQL Server environment or need assistance with performance tuning, backup strategies, or ongoing SQL Server maintenance, consider our SQL Server Managed Services. Our team of experts is here to help you streamline and secure your database operations.

Help from Database Health Monitor

msdb.dbo.backupset

The threshold for excessive backup history in the Quick Scan Report is greater than 1 year of backup history, or greater than 10,000 rows in the backup history.

SELECT MIN(backup_start_date) AS first_backup,
COUNT(*) AS total_rows
FROM msdb.dbo.backupset WITH (NOLOCK);

For those who are running frequent transaction log backups, the backup history can grow pretty quick.

To see the backup history:

SELECT&nbsp;*
FROM msdb.dbo.backupset WITH (NOLOCK);