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

Posted by John Hansen on Stack Overflow See other posts from Stack Overflow or by John Hansen
Published on 2010-06-03T22:34:21Z Indexed on 2010/06/10 14:02 UTC
Read the original article Hit count: 212

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.

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about tsql