Are all of your servers set up to clean out their backup history periodically?
Are you sure?
If you looked back at the monitor with “WTF?” written all over your face, I’ll bring you up to speed. Here’s the problem: SQL Server stores information about completed backups in the MSDB database. In older versions of SQL Server, that database isn’t indexed very well, and it can become a performance bottleneck during backups and restores. I’ve written about one particular case where I cut the company’s backup times by 2/3 just by cleaning out their backup history.
To make sure your servers are keeping their MSDB clean, you can use SQL Server Management Studio 2008’s new multi-server-query-execute feature to quickly query all of your servers. Go read my article on how to query multiple servers at once in SSMS, and then run this query against your SQL Servers:
SELECT TOP 1 backup_start_date FROM msdb.dbo.backupset WITH (NOLOCK) ORDER BY backup_set_id ASC
The query runs almost instantaneously since it’s sorted by the primary key of the table. The results of the query are shown here:
Consolidated MSDB Backup History
In that screenshot, I can see right away that three of the servers in my lab have problems. They don’t have cleanup tasks properly configured.
If you see results like this in your own environment, drop what you’re doing and go add MSDB cleanup tasks to your maintenance plans or add sp_delete_backuphistory to your T-SQL maintenance scripts. The first time it runs, you may run into some horribly long runtimes and locks, unfortunately.
Forgive me for being slow, but how can you tell those 3 have problems and not the others with dates preceeding 8/21?
Check the year field.
Check the year.
Ah, oops. However, this gives me a good idea for a blog entry.
Fantastic! I saw this when you posted it originally but didn’t have the time to check then. Glad you did a recap of it as I now had the time. Records going back years is right! I had some going back to January of 2001! I had over 200k records in one of my backupset tables. It’s a good thing we only use simple and daily full backups at this point. I set a limit on how far back we want to save, created a job to run the sp_delete_backuphistory. I also added a step to reindex the tables it touched as well. You might not need the backupfilegroup table, as it doesn’t exist on all my servers I’m nto sure why. Probably only if you have ever performed a filegroup backup I’m not sure. These could always be werapped in an exists statement too.
dbcc dbreindex (‘backupfilegroup’, ”) ;
dbcc dbreindex (‘backupfile’, ”) ;
dbcc dbreindex (‘backupmediafamily’, ”);
dbcc dbreindex (‘backupmediaset’, ”);
dbcc dbreindex (‘backupset’, ”);
dbcc dbreindex (‘restorefile’, ”);
dbcc dbreindex (‘restorefilegroup’, ”);
dbcc dbreindex (‘restorehistory’, ”);
Thanks Brent!
See http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1704.aspx for a script to more efficiently clear out the backup history.