By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,676 Members | 1,669 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,676 IT Pros & Developers. It's quick & easy.

Default "Auto Close" to no for entire server?

P: n/a
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?
Nov 27 '06 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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/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?
Nov 27 '06 #2

P: n/a
fr****************@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?
Nov 27 '06 #3

P: n/a
Beowulf (be*****************@hotmail.com) 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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 27 '06 #4

P: n/a
Erland Sommarskog wrote:
Beowulf (be*****************@hotmail.com) 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)
Nov 28 '06 #5

P: n/a
Beowulf (be*****************@hotmail.com) 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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 28 '06 #6

P: n/a
Erland Sommarskog wrote:
Beowulf (be*****************@hotmail.com) 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.
Dec 1 '06 #7

P: n/a
Erland Sommarskog wrote:
Beowulf (be*****************@hotmail.com) 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.
Dec 5 '06 #8

P: n/a
Beowulf (be*****************@hotmail.com) 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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 5 '06 #9

P: n/a
Erland Sommarskog wrote:
Beowulf (be*****************@hotmail.com) 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.
Dec 7 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.