September 9, 2013 at 9:16 am
We have log shipping setup for our primary CIS database.
Everything has been humming along fine with the restoration of the log file into the secondary database completing in 80-90 seconds.
For some reason that I've yet to determine, these log file restores are now consuming 860-870 seconds. Which has caused a bit of a backlog.
The issue does not appear to be related to the size of the log file.
Seems to be a lot of time taking place in the 'redo' phase of the restore:
2013-09-09 08:25:40.90 spid64 Recovery completed for database cisprod (database ID 6) in 866 second(s) (analysis 9 ms, redo 865900 ms, undo 42 ms.) This is an informational message only. No user action is required.
Anybody seen anything like this before?
thanks
D
September 10, 2013 at 6:52 am
Have you looked at the Wait Stats?
Also, the common question, what has changed? Has there been a service pack applied to SQL, the OS? Has something changed in the IO subsystem?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 10, 2013 at 7:14 am
We had a problem with our transaction log restores taking quite an increase due to the custom log shipping setup we had.
The DBA that set it up originally had it set to restore each log WITH STANDBY so the db was readable but it also means it also has to create the undo file. We changed the process to only restore the last log as WITH STANDBY and the rest as WITH NORECOVERY and it reduced the time dramatically.
This may not be your issue, but it is worth checking.
MCITP SQL 2005, MCSA SQL 2012
September 10, 2013 at 7:36 am
Thanks for the replies.
Not really sure that anything has changed. Still digging.
I did find a KB article that spoke to a similar problem, but the hot fix did not resolved the issue.
I re-initialized LS for that database and ran manual log restore to 'catch up'. Ran with the 'NO RECOVERY' option and all of these completed within 2 - 3 minutes.
Good I said. Re-started the log shipping jobs, and it's back to 15 minute for a restore.
So, RTaylor2208, you may be on to something here. I'll reconfigure the LS restore to use the NO RECOVERY option and monitor.
Thanks again.
D
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply