October 28, 2009 at 7:09 pm
This week I setup logshipping in my prod env to another env for around 50 dbs and all of them are in SQL 2005 instance. One of the db is taking hell lot of time to recover the db in standby mode. The db is 13GB size and logs are 5 to 7MB sizes.
When I restore full back manually it took long time and when I track the spid using the below query, the percent_complete value is reaching 100 in 2mins 15secs but after it stays more than 30mins to recover the db.
SELECT percent_complete, * FROM sys.dm_exec_requests
WHERE session_id=135
Once the db restored (after 35mins) I did the LS setup then the .TRN files taking long time to restore. even for 7MB file taking 18+mins to restore.
Initially i thought it would be some storage issue but lot of other database files resides and they are going well.
Note: The same db is being log shipped 2 other instances, they also having the same issue. Also in the primary, this db is migrated from sql 2000 and still the compatibility level is 80.
Anybody faced this kind of issues earlier or any thoughts on this to overcome?
Thanks
Jay.
Thanks
Jay
http://www.sqldbops.com
November 13, 2009 at 3:11 pm
We had an issue like this, turned out to be excessive transaction log VLFs due to log auto growth, we followed the directions in the below doc and our restore dropped from 3 hrs to 20 mins, the recovery from a sql restart also drasticly dropped in time, we had big log files, but t'ss worth a look?
Andrew
November 17, 2009 at 7:38 pm
Thanks for your tip.
Thanks
Jay
http://www.sqldbops.com
November 18, 2009 at 1:41 am
Unless you actually need the database restored with STANDBY, leave it as NO RECOVERY.
That should cut the restore time a bit, as SQL Server doesn't have to roll back any uncommitted transactions each time it restores a log file.
If your log file contains a lot of long running uncommitted transactions, the savings could be siginificant.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply