Home / server / Questions / 147132
In Process
user44650
Asked: 2010-06-02 06:53:32 +0800 CST

sql server 2005 instance unresponsive and all db's are 'in recovery'

  • 772

we've got a sql server 2005 instance that one of our guys messed up, i believe they killed the sql server service and restarted the computer, and when it came back all of our databases are "in recovery" and it times out every time we try to connect to it. it's been 'in recovery'and unable to connect to 'msbd' (also in recovery) whenever we try to use SSMC, for the last 4 days now. i'm unsure how to use the DBCC CHECKDB command to check the db integrity. we have backups(which we can't recover from because it keeps timing out), and it's a testing server, so nothing in production is really lost. is there any way to get it out of recovery mode? we have another sqlserver instance running that's just fine, but this instance keeps timing out. the errors i keep seeing are

database msdb is being recovered. wait until recovery is finished

and

an exception occurred while executing a transact-sql statement or batch Timeout expired.

any thoughts? we don't really have a DBA here, or anyone with much sql experience.

sql-server sql-server-2005

3 Answers

  1. Check the SQL Server ERRORLOG file to see if that gives any clues about the recovery process. Normally when "recovering" a database it will log it's current progress periodically into the log file with a rough ETA remaining.

    • 1

  2. You can stop the services and then delete the database files. You should be able to then restore your databases from backup. It is not normal for recovery mode to take 4 days, at least in my experience.

    I don't think you can run DBCC until recovery mode is complete.

    • 0

  3. rmyates advice is sound - SQL will write to the SQL log (and the NT log) the percentage complete, and how long to go.

    When all the user databases have stayed in recovery, I've seen this before when the disc drive holding the transaction logs (or the data files) has been swapped on a SAN. Check the timestamps of all the files to make sure they are reasonable.

    If the databases stay permanently in recovery (you have rebooted to try that?) then you'll need to look at Paul Randal's blog for definitive advice.

    • 0