After tempdb, msdb is often the most abused system database, growing unchecked until it tanks your backup reporting and job monitoring.
I’ve watched MSDB performance degrade across multiple SQL Server instances. It’s not optimized out of the box and doesn’t get much care, so as it balloons to 100GB+, metadata queries crawl, showing up as top offenders in Activity Monitor.
Another indicator of MSDB performance problems: missing indexes in MSDB showing at the top of the missing indexes DMV results.
In the past, I’d just find and add missing indexes. But MSDB tuning gets often gets overlooked even by the best of us.
I have my own reasons to keep MSDB lean and fast:
- I regularly query MSDB to get job status, backup history, and missing backups (manual + automated)
- Our enterprise backup tool frequently queries the backup history tables, about every 5 minutes. These queries have become resource-intensive and slow, significantly hindering performance on several SQL Servers. Yes, imagine that, your backup tool spends more time querying backup meta data than time it takes to do transaction log backups.
- Bloated MSDBs (50-100GB). While this may concern some, it doesn’t bother me per se..
Solution: 1) Index MSDB tables, 2) Purge old history, 3) Configure job history retention.
The missing index DMVs make the first step easy.
Here’s what they recommend on one of my production servers:
Manage MSDB Size Through Purging
I’ve written a SQL script that complements the indexing strategy by regularly purging old history records from MSDB. It uses SQL Server’s built-in system procedures for the heavy lifting, all you specify is @DaysRetention, and older records are automatically removed from key history tables. View/download it from GitHub:
Configure job history retention
You can configure automated purging of job history tables through the SSMS: SQL Server Agent ? Right-click ? Properties.
Go to the History page and tweak the values per your needs:
Click OK button at the right-bottom corner to save the changes.
That’s it. Add indexes + regular history purging + job retention settings. These three steps transformed my 100GB monsters into snappy, responsive MSDB database..
Questions, comments or tweaks? Drop them in the comments!
