auto_close

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » auto_close

auto_close

Rate Topic

Display Mode

Topic Options

Author

Message

pedro.ribeiro

Posted Sunday, May 31, 2009 9:29 AM

SSC-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

GilaMonster

Posted Sunday, May 31, 2009 9:45 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:00 AM
Points: 37,651, Visits: 29,904

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

pedro.ribeiro

Posted Sunday, May 31, 2009 9:51 AM

SSC-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

GilaMonster

Posted Sunday, May 31, 2009 9:53 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:00 AM
Points: 37,651, Visits: 29,904

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

pedro.ribeiro

Posted Sunday, May 31, 2009 9:58 AM

SSC-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 »

Permissions