SQL Server 2005 Database 'In Recovery'

views:

784

answers:

SQL Server 2005 Database 'In Recovery'

This might be a bit of a naive question and probably something I should know by now but here goes..

I restored a 35Gb database on my dev machine yesterday and it was all going fine until this morning when my client app couldn't connect. So I opened SQL Management Studio to find the database 'In Recovery'.

I don't know a huge amount about this other than it is usually something to do with uncommitted transactions. Now since i know there aren't any uncommitted transactions it must be something else. So first off, I'd like to know under what conditions this can happen. Secondly, while this is going on I can't work so if there are any ways of either stopping the recovery, speeding it up or at least finding roughly how long it's gonna be that would help.

I'm sure there's a bunch of MSSQL gurus out there who can help,..as always, it would be greatly appreciated.

In my experience I saw the same when the server crashed (by OS error or by power interruption) for big database (about 100 GB). Did you find in the event viewer some error or warning?

The only way to stop the recovery is shutdown the sql server, but when the server restart...the recovery start again!

lg 2009-09-23 08:50:07

+1 A:

Do not shut down SQL while recovery is in progress. Let it finish. Check the error logs. If it doesn't finish, restore from backup.

Randolph Potter 2009-09-23 10:27:41

Check out this as well: http://www.sqlskills.com/BLOGS/PAUL/category/Bad-Advice.aspx

Randolph Potter 2009-09-23 10:35:20

well it's not really an answer but it's the best i got it seems. Strange thing is i did just that and ran `dbcc checkdb` straight after and there was absolutely nothing wrong. Hasn't happened since either. Who knows what was up!

Stimul8d 2009-09-24 10:59:02

It could have been anything, then. Recovery usually happens when the MSSQL service doesn't terminate correctly, and databases are not checkpointed, so all logs are replayed. If you had a particularly long-running transaction at the stage that the server died, it would have to reprocess it.

Randolph Potter 2009-09-25 07:44:43

ansaurus

tags:

views:

answers:

SQL Server 2005 Database 'In Recovery'

related questions