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.

About the author

James Anderson

I'm a freelance SQL Server DBA in Southampton, UK. Performance tuning code and improving development processes are what I love to do for work. Outside of work, I live to travel to as many places as possible with my wife Sarah.

6 thoughts on “The ‘In Recovery’ Mystery”

  1. Thanks for the post – Is it an Express edition of SQL Server? I believe its set to autoclose true by default on that edition, we have had the same problem on 2014 Express at several clients

  2. When I started a DBA job about a decade ago, I was their first DBA and they had A LOT of databases. It was flat-out scary how many databases that they had that were both auto-close and auto-shrink. Personally I think those options should be removed from SSMS (perhaps keep them viewable but not changeable there) and can only be turned on or off via T-SQL.

Leave a Reply

Your email address will not be published. Required fields are marked *

Sign Up

James Anderson SQL Server DBA

Hi, I'm James Anderson. When I'm not blogging about SQL Server, I do this.

The SQL People

DBA Stackexchange Profile