Default "Auto Close" to no for entire server?

[Beowulf]

I was having this problem:

http://groups.google.com/group/microsoft.public.sqlserver.server/msg/e36e423972323378?dmode=source

with it taking an inordinate amount of time to enumerate the databases
on my local SQL Server instance, so I went through all the local
databases and unchecked "Auto Close" on the "Options" tab of the
Database properties in Enterprise Manager.

This cleared up the problem nicely. In EM or through SQL-DMO, the
databases on the local server enumerate quite quickly now. However, any
new databases that are created have "Auto Close" checked, so eventually
the original problem will resurface.

Is there some way to tell SQL Server to create any new databases with
"Auto Close" set to No?

[francois.bourdages@harfan.com]

Re: Default "Auto Close" to no for entire server?

When you create a new DB, SQL Server make a copy of database "model".
So make sure that the option auto close is set as you like in "model",
before create a new DB.


Beowulf wrote:

I was having this problem:

http://groups.google.com/group/microsoft.public.sqlserver.server/msg/e36e423972323378?dmode=source

>
with it taking an inordinate amount of time to enumerate the databases
on my local SQL Server instance, so I went through all the local
databases and unchecked "Auto Close" on the "Options" tab of the
Database properties in Enterprise Manager.
>
This cleared up the problem nicely. In EM or through SQL-DMO, the
databases on the local server enumerate quite quickly now. However, any
new databases that are created have "Auto Close" checked, so eventually
the original problem will resurface.
>
Is there some way to tell SQL Server to create any new databases with
"Auto Close" set to No?

[Beowulf]

Re: Default "Auto Close" to no for entire server?

francois.bourda ges@harfan.com wrote:

Beowulf wrote:

>I was having this problem:
>http://groups.google.com/group/micro...8?dmode=source
>>
>with it taking an inordinate amount of time to enumerate the databases
>on my local SQL Server instance, so I went through all the local
>databases and unchecked "Auto Close" on the "Options" tab of the
>Database properties in Enterprise Manager.
>>
>This cleared up the problem nicely. In EM or through SQL-DMO, the
>databases on the local server enumerate quite quickly now. However, any
>new databases that are created have "Auto Close" checked, so eventually
>the original problem will resurface.
>>
>Is there some way to tell SQL Server to create any new databases with
>"Auto Close" set to No?

>
When you create a new DB, SQL Server make a copy of database "model".
So make sure that the option auto close is set as you like in "model",
before create a new DB.

"Auto Close" is set to No in model, but if I create a new database in
Enterprise Manager, then the new database has "Auto Close" checked. Is
this a bug in SQL Server 2000?

[Erland Sommarskog]

Re: Default "Auto Close" to no for entire server?

Beowulf (beowulf_is_not _here@hotmail.c om) writes:

"Auto Close" is set to No in model, but if I create a new database in
Enterprise Manager, then the new database has "Auto Close" checked. Is
this a bug in SQL Server 2000?

What happens if you create a database from Query Analyzer, using the
CREATE DATABASE statement?

What edition of SQL 2000 do you have?



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Beowulf]

Re: Default "Auto Close" to no for entire server?

Erland Sommarskog wrote:

Beowulf (beowulf_is_not _here@hotmail.c om) writes:

>"Auto Close" is set to No in model, but if I create a new database in
>Enterprise Manager, then the new database has "Auto Close" checked. Is
>this a bug in SQL Server 2000?

>
What happens if you create a database from Query Analyzer, using the
CREATE DATABASE statement?

The result is the same as when right-clicking and choosing New Database
in EM.

The Options listed for model:
[ ] - Restrict access
(*) Members of db_owner, dbcreator, or sysadmin
( ) Single user
[ ] - Read-only
---
Recovery Model: Full
---
[ ] - ANSI NULL default
[ ] - Recursive triggers
[x] - Auto update statistics
[x] - Torn page detection
[ ] - Allow cross-database ownership chaining
[ ] - Auto close
[ ] - Auto shrink
[x] - Auto create statistics
[ ] - Use quoted identifiers
---
Compatibility Level: Database compat level 80
---
[ ] - List this database in Active Directory

What edition of SQL 2000 do you have?

SELECT @@VERSION

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Personal
Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

[Erland Sommarskog]

Re: Default "Auto Close" to no for entire server?

Beowulf (beowulf_is_not _here@hotmail.c om) writes:

The result is the same as when right-clicking and choosing New Database
in EM.
>...
SELECT @@VERSION
>
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Personal
Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Hm, Personal Edition? But Books Online says that Auto-Close is only on
by default for MSDE, so it should be off by default on Personal.

I tried flipping the AutoClose on model on my Developer Edition, and
new databases followed the setting on model. There could still be something
hardcoded for Personal, but I doubt.

I would try:

ALTER DATABASE model SET AUTO_CLOSE ON
go
ALTER DATABASE model SET AUTO_CLOSE OFF

and also restart SQL Server.

Yes, I'm clutching at straws.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Beowulf]

Re: Default "Auto Close" to no for entire server?

Erland Sommarskog wrote:

Beowulf (beowulf_is_not _here@hotmail.c om) writes:
>

>>The result is the same as when right-clicking and choosing New Database
>>in EM.
>>...
>>SELECT @@VERSION
>>
>>Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
>>23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Personal
>>Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

>
>
Hm, Personal Edition? But Books Online says that Auto-Close is only on
by default for MSDE, so it should be off by default on Personal.
>
I tried flipping the AutoClose on model on my Developer Edition, and
new databases followed the setting on model. There could still be something
hardcoded for Personal, but I doubt.
>
I would try:
>
ALTER DATABASE model SET AUTO_CLOSE ON
go
ALTER DATABASE model SET AUTO_CLOSE OFF
>
and also restart SQL Server.
>
Yes, I'm clutching at straws.

Thanks for the suggestions. I'll try that out and report back.

[Beowulf]

Re: Default "Auto Close" to no for entire server?

Erland Sommarskog wrote:

Beowulf (beowulf_is_not _here@hotmail.c om) writes:

>The result is the same as when right-clicking and choosing New Database
>in EM.
>...
>SELECT @@VERSION
>>
>Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
>23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Personal
>Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

>
Hm, Personal Edition? But Books Online says that Auto-Close is only on
by default for MSDE, so it should be off by default on Personal.
>
I tried flipping the AutoClose on model on my Developer Edition, and
new databases followed the setting on model. There could still be something
hardcoded for Personal, but I doubt.
>
I would try:
>
ALTER DATABASE model SET AUTO_CLOSE ON
go
ALTER DATABASE model SET AUTO_CLOSE OFF
>
and also restart SQL Server.
>
Yes, I'm clutching at straws.

Ok, here's what I did:

1. Re-confirmed in EM that model has Auto Close unchecked
2. Ran this SQL batch in Query Analyzer:

ALTER DATABASE model SET AUTO_CLOSE ON
go
ALTER DATABASE model SET AUTO_CLOSE OFF

CREATE DATABASE stercus1000

- Result: stercus1000 had Auto Close checked

3. Stopped the MS SQL Server service (and dependent services)
4. Started the MS SQL Server service (and dependent services)
5. Ran this SQL batch in Query Analyzer:

ALTER DATABASE model SET AUTO_CLOSE ON
go
ALTER DATABASE model SET AUTO_CLOSE OFF

CREATE DATABASE stercus2000

- Result: stercus2000 had Auto Close checked

I'm stumped.

[Erland Sommarskog]

Re: Default "Auto Close" to no for entire server?

Beowulf (beowulf_is_not _here@hotmail.c om) writes:

3. Stopped the MS SQL Server service (and dependent services)
4. Started the MS SQL Server service (and dependent services)
5. Ran this SQL batch in Query Analyzer:
>
ALTER DATABASE model SET AUTO_CLOSE ON
go
ALTER DATABASE model SET AUTO_CLOSE OFF
>
CREATE DATABASE stercus2000
>
- Result: stercus2000 had Auto Close checked
>
I'm stumped.

And you think I am not? :-)

The only I can suggest is a Post-It note which says "Don't forget to
set newly created databases to AUTO_CLOSE OFF".

I have no idea of what is going on. Maybe Personal Edition always sets
AUTO_CLOSE ON, despite that Books Online said that this applies to
MSDE. (I don't have any instance of Personal Edition installed to test.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Beowulf]

Re: Default "Auto Close" to no for entire server?

Erland Sommarskog wrote:

Beowulf (beowulf_is_not _here@hotmail.c om) writes:

>3. Stopped the MS SQL Server service (and dependent services)
>4. Started the MS SQL Server service (and dependent services)
>5. Ran this SQL batch in Query Analyzer:
>>
> ALTER DATABASE model SET AUTO_CLOSE ON
> go
> ALTER DATABASE model SET AUTO_CLOSE OFF
>>
> CREATE DATABASE stercus2000
>>
>- Result: stercus2000 had Auto Close checked
>>
>I'm stumped.

>
And you think I am not? :-)
>
The only I can suggest is a Post-It note which says "Don't forget to
set newly created databases to AUTO_CLOSE OFF".
>
I have no idea of what is going on. Maybe Personal Edition always sets
AUTO_CLOSE ON, despite that Books Online said that this applies to
MSDE. (I don't have any instance of Personal Edition installed to test.)

Thanks for the suggestions, Erland. I'll go dig out my pad of Post-Its.