AutoClose option and Personal and MSDE Edition

Hi there,
As per SQL Server's install information the AutoClose option is set to TRUE
for both the Personal and MSDE edition installations. My problem is that any
new databases created (ie snapshots) have the AutoClose option set to TRUE.
How can I change the way this option was set on installation since it's
obviously not paying attention to the model database which has the AutoClose
option set to FALSE.
Any info would be appreciated
thanksrazmanaz wrote:
> Hi there,
> As per SQL Server's install information the AutoClose option is set
> to TRUE for both the Personal and MSDE edition installations. My
> problem is that any new databases created (ie snapshots) have the
> AutoClose option set to TRUE. How can I change the way this option
> was set on installation since it's obviously not paying attention to
> the model database which has the AutoClose option set to FALSE.
> Any info would be appreciated
> thanks
AutoClose and AutoShrink default to True for both the Desktop and MSDE
editions of SQL Server. I think you need to use sp_dboption after
database creation to turn the options off. THe model database cannot
override the default settings. For MSDE, you may want to leave it on to
prevent SQL Server from using too much memory when the database is
inactive, unless this is causing a real performance issue. One way to
prevent this is to keep at least one connection open to the database at
all times while the application is running.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi David,
Thanks for responding.
However I should have mentioned that the AutoClose has to be set to FALSE
for both Personal and MSDE editions. There is no problem for resources so I
do not need to close the databases to save memory/CPU etc.
Also the sp_dboption script "sp_dboption <database>, autoclose, FALSE" won't
work because the snapshots are continually updated and the script cannot run
continually each time a new snapshot is created. This needs to be changed at
the ground floor.
So another question. If I were to install the Standard edition or higher the
install sets the default to FALSE. I just need to know where the low level
setting is so I can change it so that the changes will take effect any
database that is automatically created.
"David Gugick" wrote:
> razmanaz wrote:
> > Hi there,
> >
> > As per SQL Server's install information the AutoClose option is set
> > to TRUE for both the Personal and MSDE edition installations. My
> > problem is that any new databases created (ie snapshots) have the
> > AutoClose option set to TRUE. How can I change the way this option
> > was set on installation since it's obviously not paying attention to
> > the model database which has the AutoClose option set to FALSE.
> >
> > Any info would be appreciated
> > thanks
> AutoClose and AutoShrink default to True for both the Desktop and MSDE
> editions of SQL Server. I think you need to use sp_dboption after
> database creation to turn the options off. THe model database cannot
> override the default settings. For MSDE, you may want to leave it on to
> prevent SQL Server from using too much memory when the database is
> inactive, unless this is causing a real performance issue. One way to
> prevent this is to keep at least one connection open to the database at
> all times while the application is running.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||razmanaz wrote:
> Hi David,
> Thanks for responding.
> However I should have mentioned that the AutoClose has to be set to
> FALSE for both Personal and MSDE editions. There is no problem for
> resources so I do not need to close the databases to save memory/CPU
> etc.
> Also the sp_dboption script "sp_dboption <database>, autoclose,
> FALSE" won't work because the snapshots are continually updated and
> the script cannot run continually each time a new snapshot is
> created. This needs to be changed at the ground floor.
> So another question. If I were to install the Standard edition or
> higher the install sets the default to FALSE. I just need to know
> where the low level setting is so I can change it so that the changes
> will take effect any database that is automatically created.
>
You can't change the default on a Windows 2000/XP installation since all
you get is the Desktop Edition for those OSes AFAIK.
I'm not clear why sp_dboption won't work. What happens when you execute
the command to change the autoclose option.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi David,
The script to set the AutoClose = FALSE works with no problems when I run
it. It's just that databases in my environment get created all the time. I'm
not sitting there waiting for them and running the script on each and every
database that gets created. That's why I was asking if there was a way to
change the default so that I wouldn't have to do that.
"David Gugick" wrote:
> razmanaz wrote:
> > Hi David,
> >
> > Thanks for responding.
> > However I should have mentioned that the AutoClose has to be set to
> > FALSE for both Personal and MSDE editions. There is no problem for
> > resources so I do not need to close the databases to save memory/CPU
> > etc.
> > Also the sp_dboption script "sp_dboption <database>, autoclose,
> > FALSE" won't work because the snapshots are continually updated and
> > the script cannot run continually each time a new snapshot is
> > created. This needs to be changed at the ground floor.
> > So another question. If I were to install the Standard edition or
> > higher the install sets the default to FALSE. I just need to know
> > where the low level setting is so I can change it so that the changes
> > will take effect any database that is automatically created.
> >
> You can't change the default on a Windows 2000/XP installation since all
> you get is the Desktop Edition for those OSes AFAIK.
> I'm not clear why sp_dboption won't work. What happens when you execute
> the command to change the autoclose option.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||If model doesn't cut it, I suggest you create an Agent job that walk the databases where autoclose
is on and for each such database turn it off. Then schedule this to run every day. I'm a bit
surprised that databases are created all the time, though. Doesn't sound like the typical
environment to me...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"razmanaz" <razmanaz@.discussions.microsoft.com> wrote in message
news:EF1A3B40-5B1D-40F0-8541-5A807631BC65@.microsoft.com...
> Hi David,
> The script to set the AutoClose = FALSE works with no problems when I run
> it. It's just that databases in my environment get created all the time. I'm
> not sitting there waiting for them and running the script on each and every
> database that gets created. That's why I was asking if there was a way to
> change the default so that I wouldn't have to do that.
> "David Gugick" wrote:
>> razmanaz wrote:
>> > Hi David,
>> >
>> > Thanks for responding.
>> > However I should have mentioned that the AutoClose has to be set to
>> > FALSE for both Personal and MSDE editions. There is no problem for
>> > resources so I do not need to close the databases to save memory/CPU
>> > etc.
>> > Also the sp_dboption script "sp_dboption <database>, autoclose,
>> > FALSE" won't work because the snapshots are continually updated and
>> > the script cannot run continually each time a new snapshot is
>> > created. This needs to be changed at the ground floor.
>> > So another question. If I were to install the Standard edition or
>> > higher the install sets the default to FALSE. I just need to know
>> > where the low level setting is so I can change it so that the changes
>> > will take effect any database that is automatically created.
>> >
>> You can't change the default on a Windows 2000/XP installation since all
>> you get is the Desktop Edition for those OSes AFAIK.
>> I'm not clear why sp_dboption won't work. What happens when you execute
>> the command to change the autoclose option.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>|||razmanaz wrote:
> Hi David,
> The script to set the AutoClose = FALSE works with no problems when I
> run it. It's just that databases in my environment get created all
> the time. I'm not sitting there waiting for them and running the
> script on each and every database that gets created. That's why I was
> asking if there was a way to change the default so that I wouldn't
> have to do that.
Why can't you add the autoclose to the scripts that are used to create
the databases?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com