Something that most people don’t release needs a little TLC in SQL Server is the backup history.
Every time a database is backed up records are written about it. Over time this can add up to a lot of useless data floating around the SQL Server in the msdb database.
If you like to use the UI to restore you databases, this can also lead to the UI stalling when the restore database window comes up.
Fortunately Microsoft has provided a system stored procedure which you can use to clean up this old data. This procedure is the sp_delete_backuphistory system stored procedure. The usage of this procedure is very simple. It takes a single parameter @oldest_date which is simply the oldest date of data you want to keep. As an exmple:
EXEC sp_delete_backuphistory ‘1/1/2009’ would delete backup data older than Janunary 1, 2009.
Great Post Denny –
The only thing I would add is start small, test it out for performance and monitor what’s going on.
When I had started at a place about 4 years ago I implemented some of the maintenance jobs that I do everyhwere (including backup history removal) and I kicked off a history cleanup. Without thinking I just put the date I wanted to delete until (wanted to keep msdb small, didn’t really need that much history so went with 1 month rolling window)… Well there was a LOT of data to delete and it caused some significant performance issues which caused some really fun looking blocking chains.
This was on SQL 2000 and the code definitely changed (it used a cursor in 2000.. you can look at the difference if you do a helptext on the proc in 2000 and then in 2005/2008.. sp_helptext ‘sp_delete_backuphistory’). Even in 2005, while it is now performing set based work it is still doing all of the deletes in one transaction. If you perform a lot of backups (log backups, anyone?) there can potentially be a lot of data in that table.
I meant those tables not that table.