Solved

SQL Server 2005 is stuck "in recovery" of several databases. How can I get it to just give up or finish?

Posted on 2009-07-12
12
492 Views
Last Modified: 2012-05-07
I have shot myself in the foot with SQL Server 2005; it is stuck in recovery of several databases. I could solve everything OK myself, if I only knew how to get SQL Server 2005 to either:
a.      Give up and declare them unrecoverable, or
b.      Get on with finishing the recovery
I have backups of all the ones that are in recovery elsewhere that I can restore from.

To try to get it to get on with finishing up the recovery, I have tried several combinations of: Letting the SQL Server database service just run, along with the other SQL Server 2005 services either running or stopped, and either having Management Studio open or closed. Ive rebooted the system 2-3 times.

The net effect is that over about 6 hours, it went from one database in recovery because of pilot error to 5 of 20 in recovery, to 3 of 20 in recovery, and back to 4 of 5 in recovery.

The original pilot error was to 1) Create a new database named edaspdc; 2) Take it offline, but not detach it; 3) Replace its .mdf and .ldf files with the same files from my customers SQL Server 2005; 4) Try to put edaspdc online. It never did go online and it was all downhill from there.
Please see the attached screen shot for key info from my SQL Server Mgmt Studio
The largest database in recovery is GSAData with a 4 GB .mdf file and a 1 GB .ldf file. the others are much smaller.
My WinXP is at SP 3 plus various automatic updates so it's current.
001-SQLSvrDBs.gif
0
Comment
Question by:Douglass MacLean
  • 4
  • 3
  • 3
  • +2
12 Comments
 
LVL 13

Expert Comment

by:St3veMax
ID: 24833754
Do you have the original copies of the .mdf and .ldf files? If so, in future you dont need to create a DB, detach, then replace the files. All you need to do is put the files in the right place, right click on databases and select 'attach', point to your mdf and follow the instructions.

Bit of a longshot - what if you try 'ALTER DATABASE x WITH RECOVERY' - Does that help ?
0
 

Author Comment

by:Douglass MacLean
ID: 24833802
Hi St3veMax,
I'll follow your advice for the future; it's much simpler. Regarding your longshot, as usual, I'm getting confused by the Transact SQL documentation.

Here's what it says about Recovery:
<recovery_option> ::=
{
    RECOVERY { FULL | BULK_LOGGED | SIMPLE }
  | TORN_PAGE_DETECTION { ON | OFF }
  | PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
}

Here's what I tried and it complained about incorrect syntax neer '::='

ALTER DATABASE ProLinkModel recovery_option ::= RECOVERY SIMPLE
GO

What should the syntax be?



0
 
LVL 13

Assisted Solution

by:St3veMax
St3veMax earned 200 total points
ID: 24833808
What you've looking at above is what recovery model to set the database to, not to recover it.

To try and bring the DB online, try:

RESTORE DATABASE ProLinkModel WITH RECOVERY

This usually works if you're been restoring a database, but stopped in the middle to restore additional transaction logs, so it will bring the DB online at that point - may work in your case, may not, but worth a shot.

HTH
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 7

Assisted Solution

by:Mohed Sharfi
Mohed Sharfi earned 100 total points
ID: 24833810
Hi dmaclean2,
how are you doing, please try this to begin trace
dbcc checkdb(DB_Name) with all_errormsgs, no_infomsgs
thanks
0
 
LVL 13

Assisted Solution

by:St3veMax
St3veMax earned 200 total points
ID: 24833813
Alternatively, if you're getting confused with the syntax, you could always right click on the DB, select properties and do things like setting the recovery model via the GUI.

HTH

Steve
0
 

Accepted Solution

by:
Douglass MacLean earned 0 total points
ID: 24833838
Dear St3v3Max and mSharfi,

Please forgive me and allow me to clarify my original question.
What I really want is for the "in recovery" status go away so I can just delete the 4 problem database for now.  How can I easily tell SQL Server to just "stop it" with trying to recover?

I've tried to Detach and to Take Offline the "in recovery" databases. Nope. SQL Server says it's too busy doing something with them  (recovering) to let me do that.

My truly important databases are OK. Three of the four that are problems are far less important. I do not have backups of them immediately available, but  can get backups or customer copies sometime in the next few days. The new one, edaspdc, I can put into the SQL Data folder and attach as St3veMax suggested.
0
 
LVL 7

Assisted Solution

by:wilje
wilje earned 100 total points
ID: 24834906
First, you should review the SQL Server log to see why SQL Server is putting those databases in recovery.  There should be messages for each database showing that each database is recovering - and if you don't have those, then SQL Server is not doing anything.
You can try to finish a restore and mark the restore as done using:
RESTORE DATABASE {database} WITH RECOVERY;
I don't think this will work unless you were actually trying to restore the databases.  In that case, your next option is to just delete the databases.  Right-click on the database and delete.  Once deleted, you can restore from backups.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 24836293
>>Ive rebooted the system 2-3 times.<<
That is the cause of the Recovery mode.  Don't do that.  Your best bet is now to wait until it is complete.  depending on the size it may take all night.
0
 

Author Comment

by:Douglass MacLean
ID: 24836626
I appreciate the help from all four experts
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24843433
It would be interesting to know how long it actually took and how big are the databases.
0
 

Author Comment

by:Douglass MacLean
ID: 24845497
The largestt database was about 6 GB. I let it run all night; no progress. Here is how I solved the problem and got back to productive work:
1. Set all the SQL Server services to Manual in Administrative Tools/Services and restarted Windows
2. Moved the .mdf and .ldf files of the 4 "in recovry" databases out of the SQL Data folder to a different folder
3. Started all the SQL Server services
4. Started SQL Server Mgmt Studio; all was well with the remaining databases
5. Reset the SQL Server services to Automatic startup
6. Found useful backups of 3 problam database elsewhere and restored them; asked the customer for a current backup of the database where I originally shot myself in the foot (edaspdc).
Problem solved.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24845699
That is good to know thanks for the feed back.  Hopefully it will help someone else in future.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We have adopted the strategy to use Computers in Student Labs as the bulletin boards. The same target can be achieved by using a Login Notice feature in Group policy but it’s not as attractive as graphical wallpapers with message which grabs the att…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question