Symptom
msdb is tens of gigabytes, backups of msdb are slow, Job History in SSMS takes minutes to open, and sp_delete_backuphistory runs for hours.
What is using the space?
USE msdb;
SELECT TOP (15) t.name AS table_name,
SUM(p.rows) AS rows,
SUM(a.total_pages) * 8 / 1024 AS size_mb
FROM sys.tables t
JOIN sys.partitions p ON p.object_id = t.object_id AND p.index_id IN (0,1)
JOIN sys.allocation_units a ON a.container_id = p.partition_id
GROUP BY t.name
ORDER BY size_mb DESC;
Usual suspects:
| Table | Fed by | Purge with |
|---|---|---|
backupset, backupfile, backupmediafamily | Every backup, including frequent log backups | sp_delete_backuphistory |
sysjobhistory | Agent jobs | sp_purge_jobhistory |
sysmail_mailitems, sysmail_log | Database Mail | sysmail_delete_mailitems_sp, sysmail_delete_log_sp |
sysjobstepslogs | Log-to-table steps set to append | sp_delete_jobsteplog |
sysmaintplan_logdetail | Maintenance plans | sp_maintplan_delete_log |
One clean-up job
DECLARE @cutoff datetime = DATEADD(DAY, -90, GETDATE());
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @cutoff;
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @cutoff;
EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @cutoff;
EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @cutoff;
EXEC msdb.dbo.sp_maintplan_delete_log @oldest_time = @cutoff;
Schedule it daily. Choose the retention to match your audit needs: 30–90 days is common for backup history, longer for job history if you trend durations.
First clean-up of a huge msdb: go in batches
A single sp_delete_backuphistory across years of history can run for hours, bloat the log and block backups. Walk the date forward:
DECLARE @d datetime = (SELECT MIN(backup_start_date) FROM msdb.dbo.backupset);
DECLARE @target datetime = DATEADD(DAY, -90, GETDATE());
WHILE @d < @target
BEGIN
SET @d = DATEADD(DAY, 7, @d);
IF @d > @target SET @d = @target;
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @d;
CHECKPOINT; -- msdb is usually SIMPLE recovery
END;
Run it out of hours. It competes with backup jobs writing to the same tables.
Indexes that help
sp_delete_backuphistory joins on columns that aren't indexed by default in older versions. Many DBAs add these (they are user indexes on a system database; document them and test them first):
CREATE INDEX IX_backupset_media_set_id ON msdb.dbo.backupset (media_set_id);
CREATE INDEX IX_backupset_backup_finish_date ON msdb.dbo.backupset (backup_finish_date) INCLUDE (media_set_id);
CREATE INDEX IX_restorehistory_backup_set_id ON msdb.dbo.restorehistory (backup_set_id);
Agent's own history limits
Agent also purges by row count (Agent properties → History). If you purge by age with the job above, raise or untick the row limits so they don't cut history shorter than you expect:
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows = -1, @jobhistory_max_rows_per_job = -1;
-1 turns the row limits off. Only do that if the age-based purge job is in place.
Shrinking afterwards
Once the data is gone, the free space is reused. Shrink msdb only if the space is really needed elsewhere, and do it once, not as a scheduled step.
Tip
Back up msdb as part of your normal backups. It holds every job, schedule, alert, SSIS package (legacy) and backup history.
Prevent it next time
- The clean-up job is part of every new server build.
- Check msdb size in your weekly health check.
- Avoid "append" log-to-table settings without a purge.