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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You beat me to it!

RESTORE DATABASE AdventureWorks WITH RECOVERY
GO
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.