3

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?

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.