We recently ran into a problem with the MSDB database on one of our servers getting too big.
We have about 30 databases on this particular server, and dumpo transaction logs every 15 minutes on many of them. We have backup history clear back to 1998. Since our backup/restore plan only includes going back as far as 60 days, thevast majority of this backup history is obsolete.
So... Microsoft has supplied a wonderful little stored procedure: sp_delete_backuphistory
The problems are:
- The code is poorly written
- Any hits against the backup history tables will be table scans since there are no indexes
So, if you run EXEC sp_delete_backuphistory '07/07/07' it will literally take forever. I clocked mine at about 5 rows per minute.
In order to fix this, an index needs to be created on msdb..backupset. MS does not like it when you modify their system tables, so you can't use Enterprise Manager to do this.
Run the following in Query Analyzer:
USE msdb
CREATE INDEX temp_media_set_id
ON backupset (media_set_id)
GO
This took a while to run, but will save you a TON of time when you finally run the sp again:
EXEC sp_delete_backuphistory '07/07/07'
