Blog Post

The ‘In Recovery’ Mystery

,

Last week I received an email from one of our sysadmins asking why one of our databases kept showing as ‘In Recovery’.

SQL Server Database 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.

Starting up database multiple times

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.

The SQL Server log showed starting up database

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!

Auto close enabled on a database in SQL Server

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.

SQL Server logs after auto close switched off

#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.

Rate

Share

Share

Rate