What’s the issue?
SQL Server records every backup and restore operation in a set of history tables in the msdb system database, including backupset, backupfile, backupmediafamily, backupmediaset, restorehistory, and related tables. By default, this history accumulates indefinitely, meaning every full, differential, and log backup for every database stays in msdb forever unless explicitly purged.
This finding indicates that backup history in msdb has never been cleaned up or contains rows dating back years. On busy instances with frequent log backups, this history can grow into millions of rows over time.
Why is this a problem?
A bloated msdb database causes several practical problems. The database itself grows large, consuming storage and increasing backup size and duration for msdb. Queries against backup history slow down significantly, which affects monitoring tools, backup verification scripts, and the SSMS Restore Database dialog (which can take many minutes to open on instances with severe history bloat).
Maintenance operations on msdb such as integrity checks and index maintenance also take longer, and the size of msdb can complicate disaster recovery procedures since msdb must be restored or rebuilt as part of full instance recovery.
Beyond performance, retaining years of backup history provides little operational value. Backup history older than your retention period cannot be used for restores anyway because the underlying backup files are long gone, so the rows simply consume space without serving any purpose.
What should you do about this?
Use the built in stored procedure sp_delete_backuphistory to remove backup and restore history older than a specified date. For example, EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = ‘2025-01-01’; deletes all history older than that date. There is also sp_delete_database_backuphistory for removing all history for a specific database, useful when retiring a database.
For the first cleanup on a heavily bloated msdb, do not delete years of history in a single call. The stored procedure deletes in a single transaction and can run for hours, generating massive log activity and blocking other msdb operations. Instead, delete history in smaller chunks (for example, one month at a time, working from oldest to newest) during a maintenance window.
After the initial cleanup, schedule sp_delete_backuphistory to run regularly via a SQL Server Agent job, with a retention period that matches your actual backup retention policy (commonly 90 days, 180 days, or one year). Once the data is removed, rebuild the indexes on the affected msdb tables and consider shrinking the msdb data file if a large amount of space was reclaimed (this is one of the rare appropriate uses of shrink, since the bloat is a one time event).
Read more…
sp_delete_backuphistory (Transact-SQL) – SQL Server | Microsoft Learn