0

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?

2 Answers 2

1

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.

0

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:

2
  • 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, 2010 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. Jun 23, 2010 at 20:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .