AutoClose

How do you set the AutoClose property of a SQLExpress db? Also, can the user instance timeout property be set at the database level too to make for easier application distribution.

Is there any downside to turning off AutoClose in order to speed up the initial startup times after a period of inactivity.

I think the AutoClose property is true by default in SQL Server Express. You can look it up and set it in Management Studio Express / Databases / Database / Properties / Options.

I have a question too:
If AutoClose is true: Can then the database files be copied at any time for backup? Even when users are working with the database?
Or must all connections be closed before the database files can be copied for backup or deployment? Or must the database even be detached to be sure?

How does connection pooling has effects?
What must a developer do to be sure that the database files are in a copy able state?

I asked this a few days before, but i did not get an answer.

|||

Hi Markus,

"If AutoClose is true: Can then the database files be copied at any time for backup? Even when users are working with the database?"

No the database *could* be not consistent in the time of the copy, cause you would also need the log with possible pending transactions.

"Or must all connections be closed before the database files can be copied for backup or deployment? Or must the database even be detached to be sure? "

The safest way would be to detach the database for cpoying (or stopping the server service). You can′be sure that in the meantime of copying another user could open the database again and change something in there (unless you change the state of the database to single_user mode). ANother option for a *hot* backup (which can be done along with open connections is to use backup instead of the *cold* method trhe detaching)

"How does connection pooling has effects? "

Same for connection pooling.

"What must a developer do to be sure that the database files are in a copy able state?"

You could query the server for the database state, but you could never be sure that the database in this state during the whole copy work. The status can be retrieved using the SMO enumeration DatabaseStatus on the Database object. (un?less you change the state of the database to single_user mode)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hello Jens,

thank you very much for your answer. Ok, safest way is detach or use backup.

> No the database *could* be not consistent in the time of the
> copy, cause you would also need the log with possible
> pending transactions.

Excuse me that i have another question, but i like to understand it. When a power outage occurs during pendig transactions this is handled from Sql Server at next startup.

When an attach attaches the database in the state as it was during detach, why is this a problem then?

What is the inconsistency and why is this a problem for Sql Server? Or do you mean that there can be changes in the files during the copy and only a part of these changes are in the copied files?

Regards,
Markus

|||

Commonly spoken this is right.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||When I attach this db to Management Studio and turn off AutoClose and then detach it. Upon re-attaching the AutoClose is reverted to being True again. This is a user instance level db so I dont need it to remain attached to the SQLExpress instance. How do you set the field again so the setting sticks.
|||Acutally I think you can′t. But you have another option from the MSDN archives:

"A system administrator on the parent instance can set the duration of the time-out period for a user instance by using sp_configure to change the user instance timeout option. The default is 60 minutes."

http://msdn2.microsoft.com/en-us/ms254504.aspx

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de
|||

> Commonly spoken this is right.

Sorry, may i ask you to explain it to me more exactly? So that even i can understand it?
It is still so that i don't really understand why copy of the .mdf and .ldf file is not safe. It's only a feeling that it may be so.