?
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
Medium Priority
?
496 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 800 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 7

Assisted Solution

by:Mohed Sharfi
Mohed Sharfi earned 400 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 800 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 400 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 400 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Issue: Unstable cursor in Windows XP and Windows runs extremely slow in that any click will bring up the Hour glass (sometimes for several seconds before giving you what you want) . Troubleshooting Process and the FINAL FIX: This issue see…
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…
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…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

765 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