SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Log shipped db in suspect mode


Log shipped db in suspect mode

Author
Message
cpage 92973
cpage 92973
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 68
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.
cpage 92973
cpage 92973
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 68
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!
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)

Group: General Forum Members
Points: 125255 Visits: 18852
I'm guessing one of the log restores failed. Have you looked back through the logs to see what happened?

John
cpage 92973
cpage 92973
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 68
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
Attachments
Error.txt (21 views, 331 bytes)
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)

Group: General Forum Members
Points: 125255 Visits: 18852
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
cpage 92973
cpage 92973
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 68
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 :-)
cpage 92973
cpage 92973
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 68
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search