[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
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
?
497 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

It is only natural that we all want our PCs to be in good working order, improved system performance, so that is exactly how programs are advertised to entice. They say things like:            •      PC crashes? Get registry cleaner to repair it!    …
This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
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 video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

649 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