Posted by: Denny Cherry
Backup & recovery, Database Administration, sp_delete_backuphistory
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.