May 31, 2009 at 9:29 am
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?
May 31, 2009 at 9:45 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 31, 2009 at 9:51 am
ok.
But when is the database closed? every time the database as no query's for how much time?
May 31, 2009 at 9:53 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 31, 2009 at 9:58 am
thank you very much, i understud
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply