Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database in Principal, Synchronized/ In Recovery Expand / Collapse
Author
Message
Posted Friday, August 16, 2013 9:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 19, 2013 12:53 AM
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.
Post #1485432
Posted Saturday, August 17, 2013 2:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 40,609, Visits: 37,070
Progress should be logged into the SQL error log.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1485459
Posted Saturday, August 17, 2013 7:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 19, 2013 12:53 AM
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?
Post #1485484
Posted Saturday, August 17, 2013 7:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 40,609, Visits: 37,070
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 2008, MVP
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

Post #1485486
Posted Saturday, August 17, 2013 7:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 19, 2013 12:53 AM
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.
Post #1485488
Posted Saturday, August 17, 2013 7:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 19, 2013 12:53 AM
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?
Post #1485489
Posted Saturday, August 17, 2013 9:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 40,609, Visits: 37,070
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 2008, MVP
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

Post #1485494
Posted Saturday, August 17, 2013 10:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 19, 2013 12:53 AM
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!

Post #1485495
Posted Saturday, August 17, 2013 10:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 40,609, Visits: 37,070
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 2008, MVP
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

Post #1485501
Posted Monday, August 19, 2013 12:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 19, 2013 12:53 AM
Points: 6, Visits: 20
Thanks! Issue resolved!
Post #1485662
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse