Solved

SQL Server Database Locked with Recovery mode

Posted on 2014-10-01
10
154 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 49

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 49

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

685 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