SQL Server 2008 R2 Express - Instances continually starting up database

Question

I have 3 instances running of SQL Server 2008 R2 Express, to try and circumvent the 1gb limit for express editions. Instance 1 (SQLExpress) is attached to my busiest web site, servicing around 3000 concurrent online users. This database functions normally.

I am also running two other instances (SQLExpress1 and SQLExpress2) both attached to much quieter sites. On both instances I am seeing the databases constantly starting:

11/02/2010 20:18:24,spid53,Unknown,Starting up database 'historiaslush_forums'. 11/02/2010 19:34:43,spid52,Unknown,Starting up database 'historiaslush_forums'. 11/02/2010 19:17:17,spid54,Unknown,Starting up database 'historiaslush_forums'. 11/02/2010 19:02:37,spid51,Unknown,Starting up database 'historiaslush_forums'. 11/02/2010 18:54:27,spid51,Unknown,Starting up database 'historiaslush_forums'. 11/02/2010 18:41:27,spid51,Unknown,Starting up database 'historiaslush_forums'. 11/02/2010 17:56:28,spid51,Unknown,Starting up database 'historiaslush_forums'. 11/02/2010 17:44:13,spid52,Unknown,Starting up database 'historiaslush_forums'. 11/02/2010 17:33:25,spid53,Unknown,Starting up database 'historiaslush_forums'. 11/02/2010 17:33:21,spid53,Unknown,Starting up database 'historiaslush_forums'. 11/02/2010 17:19:04,spid51,Unknown,Starting up database 'historiaslush_forums'. 11/02/2010 17:18:55,spid53,Unknown,Starting up database 'historiaslush_forums'.

Has anyone seen this behaviour before? Is it still linked to the crippled feature set with the expression edition?

Thanks

Answer 1

By default SQL Express databases are created as AUTO_CLOSE ON. As such, they are closed as soon as the last session releases access to it. They will auto-open on next access. AUTO_CLOSE databases run an optimized recovery when opened so their openning is not as bad as a fully fledged database open. But none-the-less, you can turn AUTO_CLOSE OFF if you wish:

ALTER DATABASE historiaslush_forums SET AUTO_CLOSE OFF;

As a note, an AUTO_CLOSE ON database would behave the same on Enterprise Edition, so this is not related to Express restrictions.

Answer 2

The database you have might have the AutoClose property set on