Solved

SQL Server Database Locked with Recovery mode

Posted on 2014-10-01
10
157 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
[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
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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 50

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 50

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

734 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