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?
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.
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: