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? 9 18804
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?
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?
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)
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
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.
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.
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
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Peter Pagé |
last post by:
Hi,
I've got a window with a "<body onBlur="window.close()"> tag that keeps
closing prematurely. It happens when the user clicks on text inside a table
in the same window. Apparently IE...
|
by: John Baker |
last post by:
Hi:
I know this is a strange question, but I have inherited a system where files are copied
and records re auto numbered (as an index field) )frequently, and I am wondering how high
the number...
|
by: pw |
last post by:
Someone was using our A97 product earlier in the day. He exited it,
and now can't open the application. He is all of a sudden getting
"MSAccess.exe has encountered an error and needs to close".
...
|
by: Willem van Rumpt |
last post by:
Hi all,
coming from an unmanaged programming background, I took my time to
sort out the IDisposable and finalizer patterns. Just when I thought
I had it all conceptually neatly arranged, the...
|
by: PL |
last post by:
The Culture="auto" UICulture="auto" is a great idea, but how do you handle the situaion
where SQL Server always requires a dot "." in numeric values.
If I use Culture="auto"so that dates and...
|
by: lindanr |
last post by:
In ASP.NET 2005 I have an onblur="window.close()" javascript event in the
<body> tag. When I click on the window's scrollbar, the window closes. The
same code works fine in ASP.NET 2003.
Any...
|
by: Jefferds.A |
last post by:
Hi, I'm putting together a little database of individuals categorized
by family. I have two lists, one that contains family names and one
that contains individuals' names. When you click on a link...
|
by: nospam_news |
last post by:
I currently get asked about my usage of "auto". What is it for?
The keyword is clearly superflous here.
In contrast to the huge majority of C/C++ developers I write
definitions very explicitly...
|
by: Piotr K |
last post by:
Hi,
I've encountered a strange problem with Firefox which I don't have any
idea how to resolve. To the point: I've <divelement with a style
"height: auto" and I want to retrieve this value...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |