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.

11 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

Ellis White 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.

Stanley D. Middleton 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

ninest123 said...

burberry, tory burch outlet, jordan shoes, sac longchamp, uggs on sale, ugg boots, louis vuitton, oakley sunglasses, michael kors, tiffany jewelry, cheap oakley sunglasses, ray ban sunglasses, christian louboutin outlet, ralph lauren pas cher, louboutin pas cher, ray ban sunglasses, air max, nike free, longchamp outlet, longchamp pas cher, ray ban sunglasses, tiffany and co, oakley sunglasses, polo ralph lauren outlet, louis vuitton, prada outlet, kate spade outlet, longchamp outlet, nike free, ugg boots, nike air max, gucci outlet, oakley sunglasses, louis vuitton outlet, longchamp, nike outlet, replica watches, louis vuitton, replica watches, prada handbags, chanel handbags, louboutin outlet, oakley sunglasses, nike roshe run, louis vuitton outlet, nike air max, louboutin shoes, louboutin, polo ralph lauren outlet, air jordan pas cher

ninest123 said...

true religion jeans, hollister pas cher, vans pas cher, tn pas cher, michael kors, true religion outlet, sac guess, nike free run uk, michael kors outlet, oakley pas cher, true religion jeans, replica handbags, ugg boots, burberry outlet online, mulberry, converse pas cher, michael kors outlet, ugg boots, true religion jeans, michael kors outlet, michael kors, nike air max, air force, hogan, new balance pas cher, vanessa bruno, ray ban uk, hollister, kate spade handbags, north face, nike air max, lululemon, coach outlet, burberry, michael kors, timberland, ray ban pas cher, michael kors outlet, coach purses, lacoste pas cher, hermes, nike air max, nike blazer, north face, ralph lauren uk, abercrombie and fitch, michael kors outlet, michael kors, nike roshe, coach outlet

ninest123 said...

converse outlet, giuseppe zanotti, hollister, insanity workout, herve leger, north face outlet, asics running shoes, nike air max, louboutin, converse, instyler, vans shoes, reebok shoes, iphone cases, abercrombie and fitch, mac cosmetics, wedding dresses, nike huarache, baseball bats, p90x workout, valentino shoes, chi flat iron, nike trainers, ghd, babyliss, oakley, soccer shoes, vans, timberland boots, gucci, celine handbags, nike air max, north face outlet, new balance, ralph lauren, hollister, jimmy choo shoes, mcm handbags, birkin bag, soccer jerseys, longchamp, beats by dre, bottega veneta, ferragamo shoes, nfl jerseys, lululemon, nike roshe, hollister, mont blanc, ray ban

ninest123 said...

moncler, lancel, canada goose, moncler outlet, canada goose, louis vuitton, juicy couture outlet, swarovski crystal, sac louis vuitton pas cher, louis vuitton, canada goose outlet, moncler, moncler, moncler, pandora charms, montre pas cher, canada goose uk, ugg,ugg australia,ugg italia, hollister, canada goose, ugg,uggs,uggs canada, bottes ugg, marc jacobs, doudoune canada goose, ugg boots uk, moncler, louis vuitton, moncler, pandora jewelry, thomas sabo, pandora charms, toms shoes, barbour, coach outlet, supra shoes, replica watches, links of london, barbour jackets, canada goose, juicy couture outlet, swarovski, pandora jewelry, canada goose outlet, louis vuitton, moncler, karen millen, wedding dresses, ugg pas cher