Sql-server – New database not using the model configuration

sql serversql-server-2008-r2sql-server-express

On a SQL Server 2008 R2 Express instance, I have the "auto Close" option set to "False" on the [model] database.

However, When I execute CREATE DATABASE [test];, SQL Server creates the database with the auto close option set to true.

I checked sys.configurations just to make sure there was nothing pending (not the case).

I also tried to change the config on [model] to true, save, then change it back to false but I still have the same behavior.

If I create the database using the SSMS GUI (right click -> new database), it creates the database with the auto close option set to false

How can I make CREATE DATABASE create a database with auto close set to false?

Best Answer

I finally found the answer in Understanding SQL Express behavior: Idle time resource usage, AUTO_CLOSE and User Instances by Mike Wachal of the Microsoft SQL Express team:

Supporting ClickOnce is something we wanted to do with SQL Express, so we modified the behavior of the CREATE DATABASE functionality in SQL Express in order to set AUTO_CLOSE to True for all databases.

So I guess I'll have to put a trigger in place to change auto close back to False when a new database is created.

Related Question