Why Do I See the "In Recovery" Msg, and How Can I Prevent it?

Question

The project I'm working on creates a local copy of the SQL Server database for each SVN branch you work on. We're running SQL Server 2008 Express with Advanced Services on our local machine to host it.

When we create a new branch, the build script will create a new database with the ID of that branch, creates the schema objects, and copies over a selection of data from the production shadow server.

After the database is created, it, or other databases on the local machine, will often go into "In Recovery" mode for several minutes. After several refreshes it comes up and is happy, but will occasionally go back into "In Recovery" mode.

The database is created in simple recovery mode. The file names aren't specified, so it uses default paths for files.

The size of the database after loading data is ~400 megs. It is running in SQL Server 2005 compatibility mode.

The command that creates the database is:

sqlcmd -S $(DBServer) -Q "IF NOT EXISTS (SELECT [name] FROM sysdatabases WHERE [name] = '$(DBName)') BEGIN CREATE DATABASE [$(DBName)]; print 'Created $(DBName)'; END"

...where $(DBName) and $(DBServer) are MSBuild parameters.

I got a nice clean log file this morning. When I turned on my computer it starts all five databases. However, two of them show transactions being rolled forward and backwards. The it just keeps trying to start up all five of the databases.

2010-06-10 08:24:59.74 spid52      Starting up database 'ASPState'.
2010-06-10 08:24:59.82 spid52      Starting up database 'CommunityLibrary'.
2010-06-10 08:25:03.97 spid52      Starting up database 'DLG-R8441'.
2010-06-10 08:25:05.07 spid52      2 transactions rolled forward in database 'DLG-R8441' (6). This is an informational message only. No user action is required.
2010-06-10 08:25:05.14 spid52      0 transactions rolled back in database 'DLG-R8441' (6). This is an informational message only. No user action is required.
2010-06-10 08:25:05.14 spid52      Recovery is writing a checkpoint in database 'DLG-R8441' (6). This is an informational message only. No user action is required.
2010-06-10 08:25:11.23 spid52      Starting up database 'DLG-R8979'.
2010-06-10 08:25:12.31 spid36s     Starting up database 'DLG-R8441'.
2010-06-10 08:25:13.17 spid52      2 transactions rolled forward in database 'DLG-R8979' (9). This is an informational message only. No user action is required.
2010-06-10 08:25:13.22 spid52      0 transactions rolled back in database 'DLG-R8979' (9). This is an informational message only. No user action is required.
2010-06-10 08:25:13.22 spid52      Recovery is writing a checkpoint in database 'DLG-R8979' (9). This is an informational message only. No user action is required.
2010-06-10 08:25:18.43 spid52      Starting up database 'Rls QA'.
2010-06-10 08:25:19.13 spid46s     Starting up database 'DLG-R8979'.
2010-06-10 08:25:23.29 spid36s     Starting up database 'DLG-R8441'.
2010-06-10 08:25:27.91 spid52      Starting up database 'ASPState'.
2010-06-10 08:25:29.80 spid41s     Starting up database 'DLG-R8979'.
2010-06-10 08:25:31.22 spid52      Starting up database 'Rls QA'.

In this case it kept trying to start the databases continuously until I shut down SQL Server at 08:48:19.72, 23 minutes later. Meanwhile, I actually am able to use the databases much of the time.

The last interesting line in the log file is this...

2010-06-10 08:35:01.52 spid34s     Recovery completed for database DLG-R8441 (database ID 6) in 1 second(s) (analysis 603 ms, redo 0 ms, undo 219 ms.) This is an informational message only. No user action is required.

Answer 1

The problem appears to have been the "Auto Close" option. After I turned that off, the problem went away. The default value for that is on when you create a database in script, but off if you create the database through management studio.

The Auto Close feature releases all the resources for a database after the last user disconnects. Since this is a development machine, there's usually only one user, so it releases all the resources after each request. Also it appears as though Management Studio occasionally connects to every database on the server, resulting in each database starting up several times a minute.

Cheers