Turn Off AUTO_CLOSE

My SQL Server logs contain messages such as "2003-12-01 21:34:56 50 spid51 Starting Up Database 'IhateSpam'." Is it typical for SQL Server to constantly restart all my databases?

The AUTO_CLOSE option is probably enabled for the databases that SQL Server is regularly restarting. AUTO_CLOSE closes the database when the last user closes his or her connection and all processes in the database have completed. When a user then tries to connect to the database, SQL Server reopens the database, generating the message you describe.

By default, AUTO_CLOSE is enabled for Microsoft SQL Server Desktop Engine (MSDE) and disabled on all other SQL Server editions. MSDE is a small-footprint database, so this default makes sense in many situations. Closing the database lets SQL Server conserve memory resources, for example. However, you rarely want to enable this option on a database that multiple users regularly access because reopening the database causes delays for the users and constantly opening and closing a database consumes more resources than simply leaving the database open.

You can enable or disable the AUTO_CLOSE option by using the ALTER DATABASE command. Note that sometimes when customers move a database from MSDE to SQL Server Standard Edition, the AUTO_CLOSE option remains enabled. You can find out whether this option is on by examining the DATABASEPROPERTYEX() function's IsAutoClose property.