Solved

SQL Server Database Locked with Recovery mode

Posted on 2014-10-01
10
150 Views
Last Modified: 2014-10-01
Hi guys,
I have a issue with one of my SQL database.
This night a task failed and this morning my database is unreachable and it is written in recovery between brackets next to the database name.

Any idea on how to switch back to normal mode ?
As the disk access was overloaded, my backup didn't work... So can't restore a correct version.

Thanks you for your help.
Regards
0
Comment
Question by:MathieuSJM
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40354071
Is it mirrored?
0
 
LVL 2

Expert Comment

by:Vishal Patil
ID: 40354073
Hello MathieuSJM,

If your database is in recovery mode, run a command similar to the following to bring it online (replace AdventureWorks with the name of your database):
RESTORE DATABASE AdventureWorks WITH RECOVERY

Open in new window

0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40354075
You beat me to it!

RESTORE DATABASE AdventureWorks WITH RECOVERY
GO
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:MathieuSJM
ID: 40354077
Thanks for your replies.

@Phillip : I have a replication task within the night launched by vRanger. But it should replicate at a VM level not database.

@Vishal : Does this command will erase some datas in it ?

Thanks again for your help
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40354079
Shouldn't do. But if you have no backups, what choice do you have?

If that doesn't work, in http://social.msdn.microsoft.com/Forums/sqlserver/en-US/8dd1b91d-3e14-4486-abe6-e3a550bfe457/database-in-restoring-state-help?forum=sqldatabaseengine , someone has posted this:

1. Stop all SQL related services from windows services.

2. I opened the DATA folder where the Ldf and Mdf files resides in the SQL directory, normally its like :
"C:\Program Files\***********\MSSQL\DATA

3. Then I copied both the Ldf and Mdf files of the database:
      [db name].mdf         and        [db name]_log.ldf

I copied both of these files to another folder.

4. Then I started all the SQL related services (in step 1) again from windows services.

5. Started my MS SQL Management studio with normal login.

6. Right click on the culprit database and hit DELETE (to delete the database at all).

7. All the LDF and MDF files related to this database have gone from DATA folder (mentioned in step 2).

8. Created a new database with the same name (same name of the one I deleted in step 6 - the culprit database).

9. Then [database name]->right click -> tasks -> Take Offline.

10. I then Copied both the files (from step 3) back to the DATA folder (step 2).

11. [database name]->right click -> tasks -> Bring Online.
0
 

Author Comment

by:MathieuSJM
ID: 40354082
Indeed I don't have too many solutions unfortunately :(

Thanks, will try this and keep you posted.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40354089
Good luck - and if you fix this, make sure you have multiple backups (maybe one a day (if it's small enough), one a week, one a month etc.)
0
 
LVL 47

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40354093
Your database went in Recovery mode not in Restore mode, so the Restore command won't do nothing.

You should check SQL Server logs and Windows Event Log for errors. I bet you had at least a SQL Server service restart and your database was processing something in that moment and now it's rollback everything that wasn't committed.
You can't do nothing until the recovery process finish. After that check for VLF (Virtual Log Files) since it's the main reason for a recovery takes so long. Run DBCC LOGINFO over your database to see how many VLF's it has. I wouldn't be surprise if there are thousands of them. If so you need to review how your transaction log grows.
0
 

Author Comment

by:MathieuSJM
ID: 40354269
Guys thanks for your help.

It seems to run correctly now.
I've checked my windows event log and I could see percentage and estimated time for the recovery.

I've waited until now and it ok.

Thanks to all of you guy for your advices.
Have a nice day
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40354282
Please go and check your VLF's. If it happens again the database will be unavailable again for hours.
Kimberly Tripp's has a good article about VLF. Read it to know more about this subject and realize how this can have a negative impact on your databases.

Cheers
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

770 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