Database option "autoclose" is set. Why?

I have inherited a 2000 server with a database with "autoclose" set to
"true". It appears to fill the log with "Starting up database dbname"
messages. A search of MSDN yielded no explanation as to what this setting
really does or why one would wish to set it on. I am tempted to set it off.
Any wisdom?
Michael
Yes, it should be off.
http://groups-beta.google.com/groups...ld+autoclos e
http://www.aspfaq.com/
(Reverse address to reply.)
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:AD688CD2-0647-4B68-B887-7E12DAF066CD@.microsoft.com...
> I have inherited a 2000 server with a database with "autoclose" set to
> "true". It appears to fill the log with "Starting up database dbname"
> messages. A search of MSDN yielded no explanation as to what this setting
> really does or why one would wish to set it on. I am tempted to set it
off.
> Any wisdom?
> Michael
|||Thanks for your comments. The database in question is the unfortunate target
of Microsoft Access applications, which seems to cause all sorts of blocking
issues (Microsoft Tech Support offered little on this) and I was wondering if
my predecessor had found some relief by setting Autoclose = true. It may be
a long-shot, but with about 20 databases on this server, the one most
troublesome due to Access is the only one with autoclose = true.
Just wondering.
"Aaron [SQL Server MVP]" wrote:

> Yes, it should be off.
> http://groups-beta.google.com/groups...ld+autoclos e
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:AD688CD2-0647-4B68-B887-7E12DAF066CD@.microsoft.com...
> off.
>
>
|||On Tue, 4 Jan 2005 11:43:06 -0800, Snake wrote:

>I have inherited a 2000 server with a database with "autoclose" set to
>"true". It appears to fill the log with "Starting up database dbname"
>messages. A search of MSDN yielded no explanation as to what this setting
>really does or why one would wish to set it on.
Hi Snake,
This setting tells SQL Server to close the database when nobody is using
it. This will free some resources. It might be useful if you keep lots of
databases that are hardly ever accessed. For databases that are accessed
often, this is never a good setting - the cost of reopening the database
when someone accesses it again is simply too high.
Use this only if you keep databases with historical information or other
databases that you hardly ever need to use.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:914A7D58-5D7F-46C9-94DC-075354514385@.microsoft.com...
> Thanks for your comments. The database in question is the unfortunate
target
> of Microsoft Access applications, which seems to cause all sorts of
blocking
> issues (Microsoft Tech Support offered little on this) and I was wondering
if
> my predecessor had found some relief by setting Autoclose = true. It may
be
> a long-shot, but with about 20 databases on this server, the one most
> troublesome due to Access is the only one with autoclose = true.
>
If anything I'd guess this would make matters worse.
[vbcol=seagreen]
> Just wondering.
>
> "Aaron [SQL Server MVP]" wrote:
http://groups-beta.google.com/groups...ld+autoclos e[vbcol=seagreen]
setting[vbcol=seagreen]
it[vbcol=seagreen]
|||First of all, if you are using MSDE--hopefully not if hosting 20+ databases,
the defualt is to have AUTOCLOSE ON. However, this will incur additional
overhead as the database if brought offline and back online. Moreover, if
it is being used constantly, it will not close. The fact that you are
seeing the statements in the Error Logs means that it is closing and not
being used as much as you might think. I would set it off.
As far as the performance issues with using MS Access as a front-end to the
database has more to do with the isolation levels. If queries are being
used, make sure they are set with the SNAPSHOT and NOT the DYNASET option.
Second, every time someone opens the linked table into the grid format, MS
ACCESS will use DYNASET and LOCK the ENTIRE PAGE. This is rediculous if
someone is only browsing. You can manipulate the default lock type when
some uses the TABLE Grid to do only SNAPSHOT browsing. Then, all CRUD
(Create, Update, Delete) operations should be handled exclusively through
the use of Action Queries or SQL Pass through queries.
Sincerely,
Anthony Thomas

"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:ht5mt0pqmvqlp6ofreb5p8jg76gupsntsv@.4ax.com...
On Tue, 4 Jan 2005 11:43:06 -0800, Snake wrote:

>I have inherited a 2000 server with a database with "autoclose" set to
>"true". It appears to fill the log with "Starting up database dbname"
>messages. A search of MSDN yielded no explanation as to what this setting
>really does or why one would wish to set it on.
Hi Snake,
This setting tells SQL Server to close the database when nobody is using
it. This will free some resources. It might be useful if you keep lots of
databases that are hardly ever accessed. For databases that are accessed
often, this is never a good setting - the cost of reopening the database
when someone accesses it again is simply too high.
Use this only if you keep databases with historical information or other
databases that you hardly ever need to use.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)