Posted by Beowulf on 12/05/06 20:49

Erland Sommarskog wrote:
> Beowulf (beowulf_is_not_here@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.

[Back to original message]