I am using SQL Server Express 2008 R2.
The auto close property of a newly created database will be set to value same as model db. In my case I have set autoclose to false for model so that when I create a new database it will have autoclose set to false as well.
If I detach a database with autoclose set to false and then attach it, autoclose is set to true. I was expecting autoclose to be false since it will inherit its properties from the model database. In my case detaching and attaching a database results in the autoclose property being set to true.
Why is it so? Is there any solution to this problem?
Best Answer
Attaching a database does not involve model at all, so there is no automatic way to enforce that your model database settings are applied to any database that you later attach. This is only used for databases that you
CREATE, and even more restrictive, databases that you create withoutFOR ATTACHorFOR ATTACH_REBUILD_LOG.In Express, attached databases are automatically attached as
AutoClose = true, so there is nothing you can do with model, or the attach process itself, to set it to false. You need to either manually set it to false after attach, or I suppose you could set up a periodic job that checks if this setting is enabled on any database that has been created since the last time the job has run (or without a filter at all), and set it to false for you.Why does Express work that way? Because Express is largely designed for local development, and to conserve resources on a developer's workstation (which typically doesn't have a whole lot of resources to spare, compared to production servers), it only holds open databases that are actively being used. This topic in Books Online helps explain, though it doesn't explicitly state that any database attached to Express sets this property to true.
If you don't want to deal with this (and other Express limitations), you could always use Developer Edition.