MSDB History Not Purged
Blitz Result: MSDB History Not Purged
SQL Server tracks the history of every backup. It stores this data in the MSDB database. That’s a system database, and by default, our system databases are stored on the C drive unless specified otherwise during installation.
This poses danger in a few different ways:
The C drive might fill up, causing MSDB writes to fail. The system drive fills up more often than you might think – caused by careless users downloading huge files to their desktop (located on the system drive) or by Windows Updates downloading many files to a temporary folder on the system drive.
MSDB might grow to fill the C drive, causing Windows to fail. In cases where we’re doing very frequent transaction log backups of a large number of databases, and we never purge MSDB, this database can grow to be big enough to threaten small C drives. When the C drive fills, Windows fails hard.
The system drive is often slow. In old versions of SQL Server, the system databases weren’t indexed well. Combine this with slow local drives, and we’ve got a recipe for slow MSDB access. In one case, during our nightly backup windows, 2/3 of the time was spent just updating MSDB!
Monitoring tools may query MSDB inefficiently. We’ve seen cases where third party server monitoring tools constantly checked MSDB to make sure all databases were getting backed up, but their queries didn’t use indexes. These queries can be the slowest thing on the server.
This part of our SQL Server sp_Blitz script checks to see if there’s MSDB backup history older than 60 days. This by itself isn’t a problem – you may want to keep long periods of backup history in there just to do performance throughput reporting – but consider moving that data to Excel or your favorite reporting tool rather than hitting MSDB each time.
Return to sp_Blitz or Ask Us Questions
How to Clean Up Overloaded MSDB Backup History
Consider purging your MSDB backup history of data older than 60 days. You can do this in a couple of different ways:
- You can create a maintenance plan that runs the History Cleanup Task on a regular basis.
- You can create a simple SQL Server Agent job that runs sp_delete_backuphistory on a regular basis (you may also have it run sp_purge_jobhistory if you’d like to match the maintenance plan functionality).
Expand to the Maintenance Plan node in SSMS, right click and choose “New Maintenance Plan…”
Choose Maintenance Cleanup Task from the Toolbox window (note that sometimes this window gets hidden off to the left under Object Explorer in SSMS:
To finish, double click on the task, choose the type of cleanup you want to do, and how much data you wish to retain. I will sometimes create two tasks, because I care less about Agent Job history and Maintenance Plan history than I do about backups, and I usually want my backup history to match my data retention policy.
Either way, if your server has a large amount of history, this step will take a loooong time to run the first time. As Brent noted in the MSDB bottleneck article, you may have to resort to extreme measures like changing system database foreign keys just to get the deletes to happen.