November 9, 2005 at 9:31 am
We have an older SQL Server install with 9 databases that have been around for about 3 years. Today I had to recover the databases. Not a big deal, however using Ent. Manager Restore GUI it took approx. 5 minutes to call up the initial restore window, once I selected the db, then the db name to restore it took another 3 minutes to read the restore history. Is there an index on a table in msdb that I can add to speed this up, delete some old backup history ???
Any help would be appreciated. BTW, SQL Server 2000 w/ SP3a.
November 9, 2005 at 2:31 pm
For anyone that is interested...
sp_delete_backuphistory 09/01/05
November 10, 2005 at 11:56 am
Backup history is not deleted automatically, you should periodically run sp_delete_backuphistory to clean it out.
That stored proc can take a long time to run because it uses cursors, especially if you have a lot of old history to get rid of. It was written to be robust rather than fast. You can write your own version to eliminate the cursor. It is much faster if you delete all the old rows one table at a time, rather than handling each expired backup set and doing single-row deletes of all the related records.
November 10, 2005 at 11:59 am
Thanks. I have found the runtime is tied to the # of months and especially the # of databases / # of times a day you run full and trans. log backups. I have run this on all my SQL servers now and things are under control.
January 11, 2006 at 9:47 am
I just happened to run into this problem and found this solution, but as I was working on implementing it, I noticed a critical item:
Put a non-clustered, non-unique index on msdb.dbo.backupset.media_set_id because the first portion of the code clears the backup and restore history, and the second protion of the code cleans up backupmediafamily and backupmediaset by looking for the media_set_id in backup set, and since it is not indexed, a full table scan is done for each query, obviously the index reduces time in my case by a factor of 100.
Brian
P.S. Has anyone noticed that this subject is not usually covered in texts about backup/restore.
January 11, 2006 at 10:49 am
Slap my hand. I should know better. I thought that in the maint plans the middle section that states write history to table and you can keep that to a certain number. I guess I blindly thought that this was keeping the backup history table to the size I wanted.
Really, this should be included in the maint. plan if you ask me. Keep backup history in msdb for __ days.
January 11, 2006 at 11:36 am
I agree with you. Like I said no-one and no text ever pointed it out to me. I stumbeled across it when I deleted a database and it took for ever to go as it was deleting the backup history and then I found the other command and then I found and resurected this thread.
I do recommend the index however as it will speed up the procedure significantly. (~3000 rows 30 minutes without 30 seconds with it using an enhaced routine.)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply