Monday, May 31, 2010

SQL Server database hang in "in recovery" status

Problem. After a failed setting mirror operation (initiated from SQL Server Management Studio from GUI), SQL Server Database stay in "in recovery" status for prolonged period of time (overnight). The database is about 7GB in size. From the primary site, the database showed normal. From the secondary site, the database showed "in recovery".

There is little that can be done for the secondary database. One option is to stop the SQL services, rename the data files (.mdf & .ldf), start the SQL database again, drop the existing database, rename the data files back, and attached the data files with same database name. After that, we can try to restore database backup and set up mirror.

On a closer look from primary, it seems that the mirror is broken. However, from the output of some database operation in the secondary database, it seems that the mirror has not be broken yet. Further more, there is no entry in the event log to indicate the progress of the recovery operation.

Let's try this approach. From the primary site, to break the mirror run the following command.
ALTER DATABASE TfsVersionControl SET PARTNER OFF
After the command finished successfully, the database on the secondary site changed to (Restoring…) status immediately. The following operations are straight forward -- copy the full database backup and log, and restore with norecovery option, set mirror for the database.
This is the preferred approach, since only the problem db is touched, no need to re-set the mirror for all other databases.

There are occasional complains from the internet about this prolonged (or hanged) SQL Server database "in recovery" status. It may worth to take a look whether the database actually started the "recovery", or still in a previous status due to unknown reasons.

9 comments:

Alex said...

For repair sql files exist recovery for sql server. It utilizes high end ways of repairing sql data. The program can't modify your source data during restoration. It starts under low system requirements.

Anonymous said...

Awesome! Its really awesome paragraph, I have got much clear idea regarding from this post.



My website ... generateur de Code psn

Anonymous said...

Hi, its pleasant article on the topic of media print, we all know
media is a fantastic source of facts.

my webpage Psn Code Generator

Anonymous said...

You need to take part in a contest for one of the most useful websites on the internet.
I am going to highly recommend this website!

my website: Dragon City Hack

Anonymous said...

I simply could not depart your web site prior to
suggesting that I actually enjoyed the usual information
a person provide on your guests? Is going to be again steadily in order to investigate cross-check new posts

Feel free to visit my page: Psn Code Generator

Unknown said...

Please read this blog http://www.sqlrecoverysoftware.net/blog/sql-database-in-recovery-mode.html
Hopefully you will be able to fix this issue with it.

Unknown said...

Check out Sql database recovery which is the advance and a brilliant tool to recover both primary and secondary (MDF and NDF) database. The software is able to repair large sized MDF file with all database objects like table, views, rules, triggers, stored procedure etc. It recovers deleted SQL Server Database table’s data and supports all versions of SQL Server.

Download it now- http://www.pcrecoverytools.com/sql-database-recovery.html

Unknown said...

fitflops
polo ralph lauren outlet
nike air max
ugg outlet
los angeles angels jerseys
abercrombie and fitch
nike zoom
coach outlet store
cheap oakley sunglasses
coach outlet store
mt20180728

Unknown said...

www1126


canada goose jackets
adidas superstar
moncler jackets
christian louboutin
canada goose outlet
true religion jeans
coach outlet factory
true religion jeans outlet
kate spade outlet
salomon shoes