October 12, 2017 at 2:51 am
Hi,
I have a log shipped database on SQL 2008R2 that last night went into suspect mode & I cannot run checkdb because it reports as in the middle of a restore.
I'm wondering if anyone can point me to a way to get back into standby mode so that my log shipping can continue / I can get in to check the db without killing log shipping.
My concern is the database is log shipped from a 3rd party and getting it set up was painful so trying to get help to maximise my chance of successful recovery (am investigating the cause and logs that we've got around that & it's mitigation).
Thank you.
October 12, 2017 at 2:53 am
No I didn't mean to post this twice, after the "loading" message it looked like nothing had happened so I pressed the button again and now there are 2!
October 12, 2017 at 3:05 am
I'm guessing one of the log restores failed. Have you looked back through the logs to see what happened?
John
October 12, 2017 at 3:20 am
The log recovery completes a "writing checkpoint" entry and then I get a memory dump and a severity 16 assertion alert (which when I try to paste this reply closes so attached) - 3 google results top of which is Paul Randal saying it's a bug call Microsoft & not a great option on 2008R2.
I have exhausted the infrastructure "did something panic / die" discussion and just checking all my other bases to see if everything is looking fine and it might have just been a wobble (fingers crossed!).
Pretty much any other database we could solve but this one because it is log shipped and restarting will be so painful it's identifying how I am most likely to be able to resolve the suspect issue without then preventing continuation of log shipping.
Thanks
Chris
October 12, 2017 at 3:42 am
Chris
I suspect this isn't going to be recoverable, especially if it's down to a Microsoft bug. You could spend days trying to sort it out, or just get in touch with the third party now and look to reinitialise the log shipping. Maybe you could ask them to script everything out so that it's easier next time you need to do this?
John
October 12, 2017 at 3:44 am
Thanks - that's my suspicion too, it's not the hassle of getting log shipping running, it's the transfer of data that was truly painful & the head stomping that comes from on high because they haven't got the data they want the moment they want it 🙂
November 14, 2017 at 3:09 am
Just in case it is of interest to anyone else, I did finally solve this problem and have had a repeat and resolved again.
The fix at the time was to reset the suspect flag, restart the instance and restore the next tlog file. It seems that the failure is actually occurring during the final stages of restore and may relate to the log shipping being a third party routine that is running as a remote stored proc and being timed out.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply