December 19, 2010 at 11:50 pm
Hi,
I encounter a mirroring broke down issue. The principal databases in "In Recovery" mode and the mirror database in "Restoring" mode. My application unable to connect to the principal databases anymore.
Doesn't anyone has any how to resolve this? How can this possible to happen?
Kindly advice. Thanks
Regards,monkeyDBAWe dont grow when things are easy; We grow when we face challenges.
December 20, 2010 at 12:55 am
Did someone restart the principal? In recovery means SQL's doing crash recovery on the database, it shouldn't take long in most cases. Check the SQL error log for any messages relating to this database.
Synchronous mirroring? Asynchronous? Witness server?
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
December 20, 2010 at 9:02 pm
Hi Gail,
Thanks for your response. No one restart the principal, but the server did encounter network broke down incident. It causes the mirroring broke and resume back.
Does it possible to cause the database in "In Recovery" mode?
Kindly advice.
Regards,monkeyDBAWe dont grow when things are easy; We grow when we face challenges.
December 20, 2010 at 11:47 pm
Shouldn't do. For a db to go into recovery the db needs to be restarted (eg offline/online) or the instance or the server.
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
December 21, 2010 at 12:21 am
Hi Gail,
You mean someone should have restarted either the server or the SQL Server services? Am I correct?
Regards,monkeyDBAWe dont grow when things are easy; We grow when we face challenges.
December 21, 2010 at 12:36 am
Should? No. I'm saying that for the DB to have gone into recovery that's what would have been necessary. Can't offhand think of any other ways (short of someone running a SQL command to put the DB into recovery)
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
December 21, 2010 at 1:19 am
jimmy.liew - you can use ALTER DATABASE set ... command as a try to back the database Online again. If SQL Server is doing recovery for any reason then you need to wait until it is back.
As per BOL: RECOVERING means [Database is being recovered. The recovering process is a transient state; the database will automatically become online if the recovery succeeds. If the recovery fails, the database will become suspect. The database is unavailable.]
Thanks
December 21, 2010 at 2:44 am
Hardy21 (12/21/2010)
jimmy.liew - you can use ALTER DATABASE set ... command as a try to back the database Online again.
Not if the database is In Recovery (Recovering in sys.databases) or Restoring.
Recovering you need to wait until recovery is complete. Restoring means that a backup is restoring or the DB was restored with no recovery. It takes a restore database to change that to online.
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
April 30, 2012 at 7:47 am
I have a similar situation. Here is our setup:
Server A - usually our pricipal
Server B - usually our mirror
Server C - our witness
We had a network interruption which seemingly caused a database to failover from Server A to Server B.
The database on Server A now shows (Mirror, Synchronizing / Restoring ...)
The database on Server B now shows (In Recovery) and has been that way for 2 days.
Fortunatly we were able to bypass this database but if this were to happen to other databases we would be royally hooped.
Any idea how to recover from this? Or at least to try to figure out what the heck happened and how to prevent it in the future?
May 1, 2012 at 6:56 am
I was just going through the windows event log on Server B and found something interesting. In our setup we actually mirror, normally, 18 different databases. When we failed over from Server A to Server B i see several messages in the event log like:
The mirroring connection to "tcp://ServerA:5022" has timed out for database "DatabaseA" after 45 seconds without a response. Check the service and network connections.
Then:
Database mirroring is inactive for database 'DatabaseA'. This is an informational message only. No user action is required.
Then:
Starting up database 'DatabaseA'.
Finally:
Bypassing recovery for database 'DatabaseA' because it is marked as a mirror database, which cannot be recovered. This is an informational message only. No user action is required.
This seems to be the normal sequence of events when the databases failover.
However, for the one database that did not failover properly and is currently showing In Recovery (still) on ServerB i do not see the "Starting up database 'DbName'" message or the "Bypassing recovery ..." message.
Any thoughts?
May 1, 2012 at 7:23 am
It’s an old thread. Please start a new thread for your question. Posting the questions in old thread fragments the responses.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply