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
488 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:dmaclean2
  • 4
  • 3
  • 3
  • +2
12 Comments
 
LVL 13

Expert Comment

by:St3veMax
Comment Utility
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:dmaclean2
Comment Utility
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
Comment Utility
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
 
LVL 7

Assisted Solution

by:Mohed Sharfi
Mohed Sharfi earned 100 total points
Comment Utility
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
Comment Utility
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:
dmaclean2 earned 0 total points
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 7

Assisted Solution

by:wilje
wilje earned 100 total points
Comment Utility
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
Comment Utility
>>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:dmaclean2
Comment Utility
I appreciate the help from all four experts
0
 
LVL 75

Expert Comment

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

Author Comment

by:dmaclean2
Comment Utility
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
Comment Utility
That is good to know thanks for the feed back.  Hopefully it will help someone else in future.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
This video discusses moving either the default database or any database to a new volume.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now