SQL Server Database Locked with Recovery mode

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
MathieuSJMAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Is it mirrored?
0
 
Vishal PatilSoftware EngineerCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You beat me to it!

RESTORE DATABASE AdventureWorks WITH RECOVERY
GO
0
 
MathieuSJMAuthor Commented:
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
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
MathieuSJMAuthor Commented:
Indeed I don't have too many solutions unfortunately :(

Thanks, will try this and keep you posted.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
MathieuSJMAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.