EM "restore" option one slow dog
-
On our sql2k servers we do a full backup daily and hourly trans dumps. This has been going on for more than a year and when I try to restore a database under EM, EM "locks up" for at least 1 minute before letting me proceed. (I don't have this trouble using Query Analyzer, but a lot more typing is involved.)
I'm considering using sp_delete_backuphistory to "trim" the fat but that "sp" takes a long time to delete the entries in the backup and restore history tables.
Anyone know of a speedier way to trim the fat?
TIA,
Bill
-
Not really and I do suggest getting rid of outdated entries. However is this from a file device or Tape device? Tapes are slow because it actually reads the backupsets from the tape not the table (EM locks up when this is running so I am betting this is what you are doing), the table can however help you find the exact position you want as long as you know when the tape was put in. But it will still be slow to recover the DB.
-
Believe it or not -- it's a file device!
Bill
-
Is the file device large and is it local. Also exactly what point does the freeze occur what actions did you take, just opening restore, if so then most likely as you suggested a lot of old data slowing you down.
-
I trim all the history and backup tables in msdb every night, if you don't not only do you end up with an ever growing msdb but it does give long delays when retriving history - proceed with care and decide carfully how much of the various histories to keep.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
can you give me some more details about how you trim the history?
Thanks
-
I have a procedure that takes 3 values ( = days history to keep ) for job history, backup/restore history and sys maint history. The proc just deletes from the various tables every night. I developed the proc as we were adding over 70k records to the history tables every day and I had a 2 gb msdb. Get the sql 2k resource kit which will give you a system table map ( and a load of other useful stuff ) to help you understand the relationships of the data in msdb.
As always take care when considering anything which affects a system table.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
sp_delete_backuphistory will take a while to trim the fat if there is a lot to do. If you run this every day (I just add it to the end of my back up job) there will not be much to trim.
eg. I just added
USE msdb
-- delete old history
DECLARE @backdate datetime
set @backdate = getdate() -30
EXEC sp_delete_backuphistory @backdate
after doing a full backup to delete all old history (> 30 days). The first time this ran took a while but not now.
Francis
-
Thanks for all the help.... I was using the sp_delete_backuphistory, before writing here... The problem is the data is so old.. the msdb has got data from 2001, and 2002, and 2003.. as well as 2004..
Ok.. so I need some lashes.. But the sp being cursor based is taking forever.. At this pace it is taking about 10 minutes per day to delete..
Is there any way to wipe it oout and start over? I am doing it a few days at a time.. 5 or 6 but with about 600,000 rows.. It is taking forever..
Thanks
-
Try sp_delete_database_backuphistory 'database name' which will delete the backup history for that database. It may be faster than working with dates, but it will get rid of all history, so if you need to recovery you must know where your backup s are (at least until it gets built up again) If you have lots of databases you could use sp_MSforeachdb
Francis
-
The quickest way is to delete data from each table within msdb on an individual basis, truncating those tables you really really don't need! Don't have any active jobs running whilst you do this. The system table map in the res kit will show you which tables to clean out. As always be careful working with system tables. If you use a lot of sysmaint jobs ask yourself if you really need to write to the sysdbmaintplan_history table every time and check if all your jobs need to write to the job history table, these are usually the largest tables.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Thanks for all the help.. Does the system table map in the 2k res kit apply to 7 as well? I am running a mix of servers. The 7's are the worst ones right now.. I have run both solution.. mentioned above.. and I am down from 560,000 to about 360,000.. It has taken over 19 hrs to get there..
And I still have a lot of cleanup to do.. So.. I have to find another way.. If the system map applies to 7 then I will do it..
Thanks again.
-
Unforntunately the system table map in the 2K res kit does not apply to version 7.
Francis
-
The tables are similar and the basic principles can be applied to both 2k and 7.0. I usually move the msdb database onto the same data arrays as my oltp databases, this usually means the table clearing goes quite quick, typically it takes about 1 min on my worst case prod server to remove between 70k and 80k rows from the various tables.
You might want to defrag the tables in msdb now and again when you get time, with the agent service turned off
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
In Maintanence Plans, under the Reporting tab there is an item for the number of lines of history to keep for this maintenance plan. If this number is high, would it cause the Enterprise Manager to load the backup history slowly (this item exists in 7 & 2000)?
If so, you can reduce the number of lines saved (check each maintenance plan) and the next time an item in the maintenance plan fires, it should auto-clean.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply