Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

auto_close Expand / Collapse
Author
Message
Posted Sunday, May 31, 2009 9:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 31, 2009 10:19 AM
Points: 103, Visits: 982
Hi masters,

What does the option auto_close do exacly?

tks,

Pedro

P.s is it good to have the database in a oltp enviroment with this option on?
Post #726337
Posted Sunday, May 31, 2009 9:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:19 AM
Points: 41,558, Visits: 34,480
Books online says this:
When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.


It's a very bad idea to have it on on any database that's used a lot. Ok on something that's used maybe once a week or once a month, but if it's used a lot then anytime SQL closes the database it will have to open it again a few seconds later.
Closing a database flushes the data and procedure cache so, not only does the first query after the DB is closed have to wait for it to be brought online again, but queries will be slower than normal for a bit while the data cache is repopulated and queries and procedures are recompiled.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #726340
Posted Sunday, May 31, 2009 9:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 31, 2009 10:19 AM
Points: 103, Visits: 982
ok.
But when is the database closed? every time the database as no query's for how much time?
Post #726344
Posted Sunday, May 31, 2009 9:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:19 AM
Points: 41,558, Visits: 34,480
As I said

When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.

So, when the last connection using that database disconnects.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #726346
Posted Sunday, May 31, 2009 9:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 31, 2009 10:19 AM
Points: 103, Visits: 982
thank you very much, i understud
Post #726348
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse