August 16, 2013 at 9:02 pm
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.
August 17, 2013 at 2:32 am
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
August 17, 2013 at 7:31 am
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?
August 17, 2013 at 7:36 am
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
August 17, 2013 at 7:45 am
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.
August 17, 2013 at 7:52 am
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?
August 17, 2013 at 9:41 am
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
August 17, 2013 at 10:02 am
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!
August 17, 2013 at 10:54 am
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
August 19, 2013 at 12:55 am
Thanks! Issue resolved!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply