Last week I received an email from one of our sysadmins asking why one of our databases kept showing as ‘In Recovery’.
The database in question wasn’t stuck in recovery, it would slip in and out of the status throughout the day. Normally, I would only ever expect to see a database in recovery during a restore or after a service restart. Once recovery is complete, I would not expect to see the database slip into ‘in recovery’ again. I especially wouldn’t expect a database to keep slipping in and out of recovery.
When checking the logs I found the ‘Starting up database’ message was being logged throughout the day.
At first I thought it might be caused by our SAN snapshots using VSS but these do not shut databases down. VSS freezes IO to the data files while the snap is taken and then resumes IO once complete. No recovery is needed.
I asked myself what could cause a single database to require a recovery. Suddenly it hit me and I checked the database options.
Auto Close is right up there with running update stats after rebuilding an index!
Once I switched AUTO CLOSE off, I checked the logs and there were no more occurrences of the ‘Starting up database’ message. The last one is visible below. Just after it is the message stating that the database was switched to not use AUTO CLOSE.
#SQLNewBlogger
After I solved the problem it took me about 20 minutes to take the screenshots and write up this post. Now all I need to do is find out who configured it.
The post The ‘In Recovery’ Mystery appeared first on The Database Avenger.
