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

I have a SQL DB instance that was stopped/shut down improperly and the msdb system db along with the regular DBs have been "in recovery" for over 2 weeks now. sometimes after a reboot one DB in the instance will work for a while, while all the others still say "In Recovery", but after another reboot, it seems like different DBs are "in recovery" at that point, some that worked previously.

Can i take the MDF and LDF files and restore to a different SQL Instance and restore any of the data? I can't stop or take offline any of the databases while msdb is "in recovery" and can't figure out a way to fix the DB instance. At this point i'm looking at ways around or ways to fix it in a new instance.

thoughts?

share|improve this question

2 Answers 2

If your mssql server are recovering the msdb system db, maybe (i am not sure) you can copy the MDF and LDF files into another location (network or external drive) and then you can attach (and recovery) the copied db into another mssql server.

share|improve this answer

By definition databases are always in recovery at start up. All of them. Some complete the recovery fast, some complete it slower. Week long recovery is certainly possible and I've seen such incidents more than once. What you need to investigate why is the recovery tacking so long. Your errorlog will likely contain indications why is the recovery tacking so long. Usual culprits are very large log files that had auto-grown at a snail pace rate and contain an abnormal number of VLFs. A hardware issue could explain some of the symptoms, but is not necessarily the only explanation. Your best avenue is to investigate and find out the true cause of the long recovery operation.

Must reads:

share|improve this answer
    
thanks for the links, i'm no DBA (we don't have one) but would love to learn more while playing around with this issue. –  user44650 Jun 23 '10 at 20:01
    
Note that every time you restart the server, the msdb recovery will restart from zero (all log has to be re-analyzed). So those 6 days are not really 6 days. Other databases will experience now recovery problems simply because all the logs are probably growing like crazy, since you cannot take log backups. –  Remus Rusanu Jun 23 '10 at 20:27

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.