Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.