Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database in Principal, Synchronized/ In Recovery


Database in Principal, Synchronized/ In Recovery

Author
Message
rhyt63
rhyt63
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 20
I can't find anything in the sql error logs referencing how long the recovery is going to take. There was a network glitch and Databases failed over and some failed back automatically while others I had to manually failback but again one db is sitting in the state mentioned above. Any way to get more info? I did run the query to check the state_desc and it does say RECOVERING.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47388 Visits: 44399
Progress should be logged into the SQL error log.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


rhyt63
rhyt63
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 20
I checked the error logs again searching for any references to "recovery" and there isn't any for the database that is in this state. The log is currently filled with Logon failure errors with Error 18456 Severity 14 Status 38. Almost all of them are from the server itself. Can these stop the database from actually doing any recovery?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47388 Visits: 44399
No, they can't.

There should be messages, probably mixed up with the login failures, stating which phase of recovery the DB is in and what time is estimated left.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


rhyt63
rhyt63
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 20
I checked again and there isn't any reference as to what phase it is in for this particular database. I see all the other recovery notes for the other databases but none for this one. I did some research last night and it mentioned a "VLF" issue where it has to process all of those before you even get to the analysis phase, is that correct? Also, since the incident the drive with the database has used up an extra 5 GB.
rhyt63
rhyt63
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 20
Looking for other options besides waiting, I do have backups and the last ldf file that looks like its last write was write before the incident occurred. Quicker to restore?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47388 Visits: 44399
Wait or restore from backup. Since you have mirroring, you can break the mirroring, bring the mirror online and continue working on that, back up the mirror and then use that backup to restore the principal.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


rhyt63
rhyt63
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 20
Let me apologize ahead of time for asking a lot of questions but I definitely want to try to recover as much as possible and of course to do this all correctly.

Breaking the mirror: Run the alter database 'database name' partner off command?
Restablish the mirror: same as above with the partner on command?

I should mention the mirror side is displaying: Mirror Synchronizing\Restoring
Also, I have another database that just is sitting on the mirror server that simply says "In Recovery" and nothing else. I ran the partner off command on there but cancelled after about 25min so my concern is the possibility that the command won't work on the principal.

But if it does, then after reestablishing the mirror I should be able to have the mirror in a Mirror Synchronized\Restoring and should be able to back that up and then restore to the principal?

Thank you very much for your input by the way!
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47388 Visits: 44399
Break the mirroring, bring the mirror online. At this point there will be no more principal or mirror, just two databases on different servers. Providing the mirror doesn't also need a long recovery period, you can then work on the mirror as necessary. If the mirror also needs a long recovery time due to whatever caused it on the principal, then it too will sit recovering for however long it needs.

To re-establish the mirroring, you'll have to redo it completely, right from restore full backup.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


rhyt63
rhyt63
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 20
Thanks! Issue resolved!
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