Clean up the backup history

Published On: 2009-06-01By:

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.

Denny


Contact the Author | Contact DCAC

2 responses to “Clean up the backup history”

  1. MikeWalshSQL says:

    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.

  2. MikeWalshSQL says:

    I meant those tables not that table.

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner    Microsoft MVP    Microsoft Certified Master    VMWare Partner    VMWare vExpert
   Best Full-Service Cloud Technology Consulting Company    Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link