Purging SQL Server Backup History
It seems like every few weeks I come across a database server with a very large msdb. After a small amount of investigation work, I determine that the largest tables consuming the most space are all related to backup history.
I typically only keep 30 days of backup history but I’ve seen as little as 5 days. Backup history stored in msdb is not an audit trail that anyone should be using for keeping years worth of backup history to prove backups were taken. If something like that is needed, you should have a centralized log management system.
The data stored in msdb is just the data related to when the backup was taken, file name, lsn information, backup path, etc. It does not contain the backup itself. In my experience, 30 days is more than enough considering most organizations only keep 14 to 30 days of actual backups. The backup history is not needed to perform a restore.
I personally use Ola Hallengren’s scripts for database maintenance as often as possible. Most of the time I get to use his entire maintenance solution that includes SQL Agent jobs, one of those is for deleting backup history. When I find other custom solutions or 3rd party backup solutions that do not include purging backup history, I just create a SQL Agent job with the following code against msdb:
| declare @date datetime SET @date = dateadd(d, -30, getdate()) exec sp_delete_backuphistory @date |
Just change the -30 to whatever retention time you need.
Leave a Reply