Last week I visited our client who has pretty big databases and performs BACKUP LOG ..operation on almost all user databases. Now, one of the most critical databases got corrupted and the DBA was pretty confident that he won’t loose any data (as he had backup of log file) and brings the database from the backup within 10-12 minutes.
They also have very well written stored procedure that does RESTORE DATABASE based on name of the database and number of log files to be restored. They run the stored procedure and it has been running for almost 5 hours till DBA canceled the process. What happened? Why it has taken so much time? I thought about it and asked him a question,:-”Have you ever cleared backup history?”, he replied that he hasn’t. Then we checked backupset system database that contained more than one million rows!!!!
I remember SQL Server MVP Geoff N.Hiten wrote the blog about the issue and I even posted a comment on.Please check the following article
http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx
It tooks only 3 minutes to run a script that create indexes and about 15 munutes to run
use msdb
go
declare @OldestDate datetime
set @OldestDate = getdate() -100
exec sp_delete_backuphistory @OldestDate
Now that it is finished , our RESTORE command took only 12 minutes to complete.
I’d like to point out how important is to clear backup history (Fortunately, in SQL Server 2005 we have builtin taks to do the job) as on time ‘X’ you will succefully restore a needed database.
