Cleanup Backup History
While looking at long running queries today I noticed that one of our worst performing queries was a monitoring tool that checked when the last backup ran.
SELECT database_name = DB_NAME(DB_ID([database_name])) , type = [type] , backup_start_date = MAX([backup_start_date]) , backup_finish_date = MAX([backup_finish_date]) FROM msdb..backupset WHERE DB_ID([database_name]) IS NOT NULL GROUP BY DB_NAME(DB_ID([database_name])) ,[type]
I had a look at the query plan and it was pretty simple:
It does a scan on the clustered index looking for the biggest start and finish dates for backups on each database. Not ideal, but surely it can’t be that bad right?
I had a look at options to optimize the query, we can’t change the query because it’s in a monitoring tool that we don’t have access to, we can’t easily change the table structure because it’s in msdb, we could add an index, but there aren’t really any good indexes to add that will make it a lot faster. How do we make this query faster?
On querying the backupset table I noticed that we had records going back to when the server was first commissioned. We do nearly 200,000 backups on that server in a month made up of Full, Differential and very frequent Log backups. Doing the maths, that makes…well my maths isn’t that good, so lets just say it’s a lot of backups, nearly 5 million records! Not the biggest table ever by any stretch of imagination, but doing a scan on 5 million rows regularly is not ideal.
I’m not sure what your requirements are but for us, keeping history back to day one is not required. We don’t even have most of those backup files anymore (we definitely don’t keep log backups that long). We use Ola Hallengren’s backup and maintenance scripts (you should too) and it turns out that Ola had thought about this problem too, and there was a simple solution. As part of the jobs that he has, there is job called sp_delete_backuphistory which runs sp_delete_backuphistory which we now have scheduled to run every month. We now only have the last 3 months of data and the query returns in about 1 second – good enough for a monitoring tool.
A quick warning though in enabling the job. If this is the first time it’s been run and you are cleaning up millions of backup records then watch out that your MSDB log file doesn’t fill the drive. Our log file went from a tiny 200mb to 15GB in under a minute where it filled the drive. Fortunately we were able to get it under control before other critical databases went offline. We ended up calling sp_delete_backuphistory manually in monthly blocks so that we didn’t take the server offline.