Found an interesting thing that others have run across but it is the first time I’ve seen it.

A customer emailed to say that the SQL 2008 db that I had helped him with seemed to be going into recovery mode on a regular basis while watching the SQL Management Studio screen.

Needless to say he was a bit nervous and about to take some drastic steps. Eventually he found that the Auto Close option was set to true.

When this is set to true, the database automatically closes all connections and unlocks the mdf file after 300 milliseconds. When a new connection is made it spins backup… Great for xcopy deployment on a client machine but not a multi-user server based application.

So the warning… if you have started a database with SQL express and then move it to a production SQL server, make sure you check that the Auto Close option is set to false.

See options screen below: