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:
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.