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
Showing posts with label autoclose. Show all posts
Showing posts with label autoclose. Show all posts
Sunday, February 19, 2012
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
thanks
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
|||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:
> 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:
> 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...[vbcol=seagreen]
> 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,
> 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
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
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
|||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:
> 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:
> 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...[vbcol=seagreen]
> 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,
> 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
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:
> 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:
> 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 dat
abases where autoclose
is on and for each such database turn it off. Then schedule this to run ever
y day. I'm a bit
surprised that databases are created all the time, though. Doesn't sound lik
e 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...[vbcol=seagreen]
> 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 ever
y
> 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,
> 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
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:
> 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:
> 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 dat
abases where autoclose
is on and for each such database turn it off. Then schedule this to run ever
y day. I'm a bit
surprised that databases are created all the time, though. Doesn't sound lik
e 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...[vbcol=seagreen]
> 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 ever
y
> 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,
> 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
AutoClose - How much resources are saved
We have a server with a number of relatively infrequently used DBs, one or
two are more archival.
If I set them to Autclose, any idea as to resources released ?
I know the users will suffer when they do access them, but some never really
are used.
KlK, MCSE
> I know the users will suffer when they do access them, but some never
really
> are used.
Then wouldn't it make more sense to detach them?
|||Here you go:
http://msdn.microsoft.com/library/de...ar_ts_1o4z.asp
Sincerely,
Anthony Thomas
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE
|||note that every time you access EM and open the Database tree/folder, all the
databases that were closed will re-open and it also gets logged... I would
follow Aaron advice.
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE
|||Well unfortunately, while I say they are really never used, the users would
not agree and want their data. So I guess we leave as is.
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE
|||> Well unfortunately, while I say they are really never used, the users
would
> not agree and want their data. So I guess we leave as is.
So what is the big concern about moving them or having them autoclose or not
then?
If having them on the same server as other databases is such a big concern,
why not detach them from that server and attach them to a different one?
two are more archival.
If I set them to Autclose, any idea as to resources released ?
I know the users will suffer when they do access them, but some never really
are used.
KlK, MCSE
> I know the users will suffer when they do access them, but some never
really
> are used.
Then wouldn't it make more sense to detach them?
|||Here you go:
http://msdn.microsoft.com/library/de...ar_ts_1o4z.asp
Sincerely,
Anthony Thomas
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE
|||note that every time you access EM and open the Database tree/folder, all the
databases that were closed will re-open and it also gets logged... I would
follow Aaron advice.
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE
|||Well unfortunately, while I say they are really never used, the users would
not agree and want their data. So I guess we leave as is.
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE
|||> Well unfortunately, while I say they are really never used, the users
would
> not agree and want their data. So I guess we leave as is.
So what is the big concern about moving them or having them autoclose or not
then?
If having them on the same server as other databases is such a big concern,
why not detach them from that server and attach them to a different one?
AutoClose - How much resources are saved
We have a server with a number of relatively infrequently used DBs, one or
two are more archival.
If I set them to Autclose, any idea as to resources released ?
I know the users will suffer when they do access them, but some never really
are used.
KlK, MCSE> I know the users will suffer when they do access them, but some never
really
> are used.
Then wouldn't it make more sense to detach them?|||Here you go:
http://msdn.microsoft.com/library/d...br />
1o4z.asp
Sincerely,
Anthony Thomas
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never real
ly
> are used.
> --
> KlK, MCSE|||note that every time you access EM and open the Database tree/folder, all th
e
databases that were closed will re-open and it also gets logged... I would
follow Aaron advice.
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never real
ly
> are used.
> --
> KlK, MCSE|||Well unfortunately, while I say they are really never used, the users would
not agree and want their data. So I guess we leave as is.
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never real
ly
> are used.
> --
> KlK, MCSE|||> Well unfortunately, while I say they are really never used, the users
would
> not agree and want their data. So I guess we leave as is.
So what is the big concern about moving them or having them autoclose or not
then?
If having them on the same server as other databases is such a big concern,
why not detach them from that server and attach them to a different one?
two are more archival.
If I set them to Autclose, any idea as to resources released ?
I know the users will suffer when they do access them, but some never really
are used.
KlK, MCSE> I know the users will suffer when they do access them, but some never
really
> are used.
Then wouldn't it make more sense to detach them?|||Here you go:
http://msdn.microsoft.com/library/d...br />
1o4z.asp
Sincerely,
Anthony Thomas
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never real
ly
> are used.
> --
> KlK, MCSE|||note that every time you access EM and open the Database tree/folder, all th
e
databases that were closed will re-open and it also gets logged... I would
follow Aaron advice.
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never real
ly
> are used.
> --
> KlK, MCSE|||Well unfortunately, while I say they are really never used, the users would
not agree and want their data. So I guess we leave as is.
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never real
ly
> are used.
> --
> KlK, MCSE|||> Well unfortunately, while I say they are really never used, the users
would
> not agree and want their data. So I guess we leave as is.
So what is the big concern about moving them or having them autoclose or not
then?
If having them on the same server as other databases is such a big concern,
why not detach them from that server and attach them to a different one?
AutoClose - How much resources are saved
We have a server with a number of relatively infrequently used DBs, one or
two are more archival.
If I set them to Autclose, any idea as to resources released ?
I know the users will suffer when they do access them, but some never really
are used.
--
KlK, MCSE> I know the users will suffer when they do access them, but some never
really
> are used.
Then wouldn't it make more sense to detach them?|||Here you go:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_1o4z.asp
Sincerely,
Anthony Thomas
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE|||note that every time you access EM and open the Database tree/folder, all the
databases that were closed will re-open and it also gets logged... I would
follow Aaron advice.
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE|||Well unfortunately, while I say they are really never used, the users would
not agree and want their data. So I guess we leave as is.
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE|||> Well unfortunately, while I say they are really never used, the users
would
> not agree and want their data. So I guess we leave as is.
So what is the big concern about moving them or having them autoclose or not
then?
If having them on the same server as other databases is such a big concern,
why not detach them from that server and attach them to a different one?
two are more archival.
If I set them to Autclose, any idea as to resources released ?
I know the users will suffer when they do access them, but some never really
are used.
--
KlK, MCSE> I know the users will suffer when they do access them, but some never
really
> are used.
Then wouldn't it make more sense to detach them?|||Here you go:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_1o4z.asp
Sincerely,
Anthony Thomas
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE|||note that every time you access EM and open the Database tree/folder, all the
databases that were closed will re-open and it also gets logged... I would
follow Aaron advice.
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE|||Well unfortunately, while I say they are really never used, the users would
not agree and want their data. So I guess we leave as is.
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE|||> Well unfortunately, while I say they are really never used, the users
would
> not agree and want their data. So I guess we leave as is.
So what is the big concern about moving them or having them autoclose or not
then?
If having them on the same server as other databases is such a big concern,
why not detach them from that server and attach them to a different one?
AutoClose
How do you set the AutoClose property of a SQLExpress db? Also, can the user instance timeout property be set at the database level too to make for easier application distribution.
|||Acutally I think you can′t. But you have another option from the MSDN archives:
Is there any downside to turning off AutoClose in order to speed up the initial startup times after a period of inactivity.
I think the AutoClose property is true by default in SQL Server Express. You can look it up and set it in Management Studio Express / Databases / Database / Properties / Options.
I have a question too:
If AutoClose is true: Can then the database files be copied at any time for backup? Even when users are working with the database?
Or must all connections be closed before the database files can be copied for backup or deployment? Or must the database even be detached to be sure?
How does connection pooling has effects?
What must a developer do to be sure that the database files are in a copy able state?
I asked this a few days before, but i did not get an answer.
|||
Hi Markus,
"If AutoClose is true: Can then the database files be copied at any time for backup? Even when users are working with the database?"
No the database *could* be not consistent in the time of the copy, cause you would also need the log with possible pending transactions.
"Or must all connections be closed before the database files can be copied for backup or deployment? Or must the database even be detached to be sure? "
The safest way would be to detach the database for cpoying (or stopping the server service). You can′be sure that in the meantime of copying another user could open the database again and change something in there (unless you change the state of the database to single_user mode). ANother option for a *hot* backup (which can be done along with open connections is to use backup instead of the *cold* method trhe detaching)
"How does connection pooling has effects? "
Same for connection pooling.
"What must a developer do to be sure that the database files are in a copy able state?"
You could query the server for the database state, but you could never be sure that the database in this state during the whole copy work. The status can be retrieved using the SMO enumeration DatabaseStatus on the Database object. (un?less you change the state of the database to single_user mode)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||Hello Jens,
thank you very much for your answer. Ok, safest way is detach or use backup.
> No the database *could* be not consistent in the time of the
> copy, cause you would also need the log with possible
> pending transactions.
Excuse me that i have another question, but i like to understand it. When a power outage occurs during pendig transactions this is handled from Sql Server at next startup.
When an attach attaches the database in the state as it was during detach, why is this a problem then?
What is the inconsistency and why is this a problem for Sql Server? Or do you mean that there can be changes in the files during the copy and only a part of these changes are in the copied files?
Regards,
Markus
Commonly spoken this is right.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||Acutally I think you can′t. But you have another option from the MSDN archives:
"A system administrator on the parent instance can set the duration of the time-out period for a user instance by using sp_configure to change the user instance timeout option. The default is 60 minutes."
http://msdn2.microsoft.com/en-us/ms254504.aspx
HTH; Jens Suessmeyer.
http://www.sqlserver2005.de
|||
> Commonly spoken this is right.
Sorry, may i ask you to explain it to me more exactly? So that even i can understand it?
It is still so that i don't really understand why copy of the .mdf and .ldf file is not safe. It's only a feeling that it may be so.
AUTOCLOSE
What does autoclose do ?
And why Starting up database message is repeated when autoclose is
true?"AMBROSE" <jeyaprabhu.j@.gmail.com> wrote in message
news:1172635162.065647.3570@.z35g2000cwz.googlegroups.com...
> What does autoclose do ?
> And why Starting up database message is repeated when autoclose is
> true?
>
If SQL Server doesn't detect usage of the said database in some interval
(can't recall what it is) it "closes" the database.
Then next time there's access, it reopens it, hence the startup up messages.
I recommend keeping autoclose turned OFF.
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||This database option "closes" the database file whenever the last users
disconnects from the database. The resources are freed up,
but when a new user connects to the server, the database is reopened and
takes up some resources. So it is good to make this option turned
off for all databases in a production server.
Thanks
Hari
"AMBROSE" <jeyaprabhu.j@.gmail.com> wrote in message
news:1172635162.065647.3570@.z35g2000cwz.googlegroups.com...
> What does autoclose do ?
> And why Starting up database message is repeated when autoclose is
> true?
>|||On Feb 28, 9:56 am, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> This database option "closes" the database file whenever the last users
> disconnects from the database. The resources are freed up,
> but when a new user connects to the server, the database is reopened and
> takes up some resources. So it is good to make this option turned
> off for all databases in a production server.
> Thanks
> Hari
> "AMBROSE" <jeyaprabh...@.gmail.com> wrote in message
> news:1172635162.065647.3570@.z35g2000cwz.googlegroups.com...
> > What does autoclose do ?
> > And why Starting up database message is repeated when autoclose is
> > true?
Will autoclose option set to true create any login problem?|||> Will autoclose option set to true create any login problem?
It shouldn't and I haven't heard of such.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AMBROSE" <jeyaprabhu.j@.gmail.com> wrote in message
news:1172639991.878459.94420@.h3g2000cwc.googlegroups.com...
> On Feb 28, 9:56 am, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
>> This database option "closes" the database file whenever the last users
>> disconnects from the database. The resources are freed up,
>> but when a new user connects to the server, the database is reopened and
>> takes up some resources. So it is good to make this option turned
>> off for all databases in a production server.
>> Thanks
>> Hari
>> "AMBROSE" <jeyaprabh...@.gmail.com> wrote in message
>> news:1172635162.065647.3570@.z35g2000cwz.googlegroups.com...
>> > What does autoclose do ?
>> > And why Starting up database message is repeated when autoclose is
>> > true?
> Will autoclose option set to true create any login problem?
>|||Hi
"AMBROSE" wrote:
> On Feb 28, 9:56 am, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> > This database option "closes" the database file whenever the last users
> > disconnects from the database. The resources are freed up,
> > but when a new user connects to the server, the database is reopened and
> > takes up some resources. So it is good to make this option turned
> > off for all databases in a production server.
> >
> > Thanks
> > Hari
> >
> > "AMBROSE" <jeyaprabh...@.gmail.com> wrote in message
> >
> > news:1172635162.065647.3570@.z35g2000cwz.googlegroups.com...
> >
> > > What does autoclose do ?
> >
> > > And why Starting up database message is repeated when autoclose is
> > > true?
> Will autoclose option set to true create any login problem?
>
If you have Anti-Virus software scanning your database files there can be
problems opening the database if a scan is occurring.
You may want to read http://support.microsoft.com/default.aspx/kb/309422
John|||On Feb 27, 11:19 pm, "AMBROSE" <jeyaprabh...@.gmail.com> wrote:
> Will autoclose option set to true create any login problem?
If the "default" database for a given login is "closed", and something
prevents it from being "opened", that login will fail.|||> Will autoclose option set to true create any login problem?
Sure, in addition to the other mentioned problems, if it takes a long time
to re-open the database, the login attempt may time out.
I strongly recommend NOT using autoclose.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
And why Starting up database message is repeated when autoclose is
true?"AMBROSE" <jeyaprabhu.j@.gmail.com> wrote in message
news:1172635162.065647.3570@.z35g2000cwz.googlegroups.com...
> What does autoclose do ?
> And why Starting up database message is repeated when autoclose is
> true?
>
If SQL Server doesn't detect usage of the said database in some interval
(can't recall what it is) it "closes" the database.
Then next time there's access, it reopens it, hence the startup up messages.
I recommend keeping autoclose turned OFF.
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||This database option "closes" the database file whenever the last users
disconnects from the database. The resources are freed up,
but when a new user connects to the server, the database is reopened and
takes up some resources. So it is good to make this option turned
off for all databases in a production server.
Thanks
Hari
"AMBROSE" <jeyaprabhu.j@.gmail.com> wrote in message
news:1172635162.065647.3570@.z35g2000cwz.googlegroups.com...
> What does autoclose do ?
> And why Starting up database message is repeated when autoclose is
> true?
>|||On Feb 28, 9:56 am, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> This database option "closes" the database file whenever the last users
> disconnects from the database. The resources are freed up,
> but when a new user connects to the server, the database is reopened and
> takes up some resources. So it is good to make this option turned
> off for all databases in a production server.
> Thanks
> Hari
> "AMBROSE" <jeyaprabh...@.gmail.com> wrote in message
> news:1172635162.065647.3570@.z35g2000cwz.googlegroups.com...
> > What does autoclose do ?
> > And why Starting up database message is repeated when autoclose is
> > true?
Will autoclose option set to true create any login problem?|||> Will autoclose option set to true create any login problem?
It shouldn't and I haven't heard of such.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AMBROSE" <jeyaprabhu.j@.gmail.com> wrote in message
news:1172639991.878459.94420@.h3g2000cwc.googlegroups.com...
> On Feb 28, 9:56 am, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
>> This database option "closes" the database file whenever the last users
>> disconnects from the database. The resources are freed up,
>> but when a new user connects to the server, the database is reopened and
>> takes up some resources. So it is good to make this option turned
>> off for all databases in a production server.
>> Thanks
>> Hari
>> "AMBROSE" <jeyaprabh...@.gmail.com> wrote in message
>> news:1172635162.065647.3570@.z35g2000cwz.googlegroups.com...
>> > What does autoclose do ?
>> > And why Starting up database message is repeated when autoclose is
>> > true?
> Will autoclose option set to true create any login problem?
>|||Hi
"AMBROSE" wrote:
> On Feb 28, 9:56 am, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> > This database option "closes" the database file whenever the last users
> > disconnects from the database. The resources are freed up,
> > but when a new user connects to the server, the database is reopened and
> > takes up some resources. So it is good to make this option turned
> > off for all databases in a production server.
> >
> > Thanks
> > Hari
> >
> > "AMBROSE" <jeyaprabh...@.gmail.com> wrote in message
> >
> > news:1172635162.065647.3570@.z35g2000cwz.googlegroups.com...
> >
> > > What does autoclose do ?
> >
> > > And why Starting up database message is repeated when autoclose is
> > > true?
> Will autoclose option set to true create any login problem?
>
If you have Anti-Virus software scanning your database files there can be
problems opening the database if a scan is occurring.
You may want to read http://support.microsoft.com/default.aspx/kb/309422
John|||On Feb 27, 11:19 pm, "AMBROSE" <jeyaprabh...@.gmail.com> wrote:
> Will autoclose option set to true create any login problem?
If the "default" database for a given login is "closed", and something
prevents it from being "opened", that login will fail.|||> Will autoclose option set to true create any login problem?
Sure, in addition to the other mentioned problems, if it takes a long time
to re-open the database, the login attempt may time out.
I strongly recommend NOT using autoclose.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
AUTOCLOSE
What does autoclose do ?
And why Starting up database message is repeated when autoclose is
true?
"AMBROSE" <jeyaprabhu.j@.gmail.com> wrote in message
news:1172635162.065647.3570@.z35g2000cwz.googlegrou ps.com...
> What does autoclose do ?
> And why Starting up database message is repeated when autoclose is
> true?
>
If SQL Server doesn't detect usage of the said database in some interval
(can't recall what it is) it "closes" the database.
Then next time there's access, it reopens it, hence the startup up messages.
I recommend keeping autoclose turned OFF.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||This database option "closes" the database file whenever the last users
disconnects from the database. The resources are freed up,
but when a new user connects to the server, the database is reopened and
takes up some resources. So it is good to make this option turned
off for all databases in a production server.
Thanks
Hari
"AMBROSE" <jeyaprabhu.j@.gmail.com> wrote in message
news:1172635162.065647.3570@.z35g2000cwz.googlegrou ps.com...
> What does autoclose do ?
> And why Starting up database message is repeated when autoclose is
> true?
>
|||On Feb 28, 9:56 am, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:[vbcol=seagreen]
> This database option "closes" the database file whenever the last users
> disconnects from the database. The resources are freed up,
> but when a new user connects to the server, the database is reopened and
> takes up some resources. So it is good to make this option turned
> off for all databases in a production server.
> Thanks
> Hari
> "AMBROSE" <jeyaprabh...@.gmail.com> wrote in message
> news:1172635162.065647.3570@.z35g2000cwz.googlegrou ps.com...
>
Will autoclose option set to true create any login problem?
|||Hi
"AMBROSE" wrote:
> On Feb 28, 9:56 am, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Will autoclose option set to true create any login problem?
>
If you have Anti-Virus software scanning your database files there can be
problems opening the database if a scan is occurring.
You may want to read http://support.microsoft.com/default.aspx/kb/309422
John
|||On Feb 27, 11:19 pm, "AMBROSE" <jeyaprabh...@.gmail.com> wrote:
> Will autoclose option set to true create any login problem?
If the "default" database for a given login is "closed", and something
prevents it from being "opened", that login will fail.
|||> Will autoclose option set to true create any login problem?
Sure, in addition to the other mentioned problems, if it takes a long time
to re-open the database, the login attempt may time out.
I strongly recommend NOT using autoclose.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
And why Starting up database message is repeated when autoclose is
true?
"AMBROSE" <jeyaprabhu.j@.gmail.com> wrote in message
news:1172635162.065647.3570@.z35g2000cwz.googlegrou ps.com...
> What does autoclose do ?
> And why Starting up database message is repeated when autoclose is
> true?
>
If SQL Server doesn't detect usage of the said database in some interval
(can't recall what it is) it "closes" the database.
Then next time there's access, it reopens it, hence the startup up messages.
I recommend keeping autoclose turned OFF.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||This database option "closes" the database file whenever the last users
disconnects from the database. The resources are freed up,
but when a new user connects to the server, the database is reopened and
takes up some resources. So it is good to make this option turned
off for all databases in a production server.
Thanks
Hari
"AMBROSE" <jeyaprabhu.j@.gmail.com> wrote in message
news:1172635162.065647.3570@.z35g2000cwz.googlegrou ps.com...
> What does autoclose do ?
> And why Starting up database message is repeated when autoclose is
> true?
>
|||On Feb 28, 9:56 am, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:[vbcol=seagreen]
> This database option "closes" the database file whenever the last users
> disconnects from the database. The resources are freed up,
> but when a new user connects to the server, the database is reopened and
> takes up some resources. So it is good to make this option turned
> off for all databases in a production server.
> Thanks
> Hari
> "AMBROSE" <jeyaprabh...@.gmail.com> wrote in message
> news:1172635162.065647.3570@.z35g2000cwz.googlegrou ps.com...
>
Will autoclose option set to true create any login problem?
|||Hi
"AMBROSE" wrote:
> On Feb 28, 9:56 am, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Will autoclose option set to true create any login problem?
>
If you have Anti-Virus software scanning your database files there can be
problems opening the database if a scan is occurring.
You may want to read http://support.microsoft.com/default.aspx/kb/309422
John
|||On Feb 27, 11:19 pm, "AMBROSE" <jeyaprabh...@.gmail.com> wrote:
> Will autoclose option set to true create any login problem?
If the "default" database for a given login is "closed", and something
prevents it from being "opened", that login will fail.
|||> Will autoclose option set to true create any login problem?
Sure, in addition to the other mentioned problems, if it takes a long time
to re-open the database, the login attempt may time out.
I strongly recommend NOT using autoclose.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
AUTOCLOSE
It has been suggested to me by one of my vendors that I
need to have AUTOCLOSE turned on for my SQL Server 2000
backups to be reliable. I have never heard this before.
Does anyone feel AUTOCLOSE needs to be set to True for
Enterprise Manager/Agent/Jobs backups to be reliable?
Please explain.
ThanksThat is complete rubbish. :-)
Your vendor apparently think that you do backup through nicking the database
files at the file system level...
Even if you did, autoclose is not, AFAIK, documented to work for this. I nev
er recommend to do backup of the
database files, but if someone would do that, you need to shutdown SQL Serve
r.
(And convincing me of the opposite requires a BOL topic or a KB article ;-).
Such may very well exists, and I
may just have missed it, of course.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Johnny Kennedy" <jkennedy@.pcmh.com> wrote in message news:12a7a01c411b6$f56bf6b0$a401280a@.
phx.gbl...
> It has been suggested to me by one of my vendors that I
> need to have AUTOCLOSE turned on for my SQL Server 2000
> backups to be reliable. I have never heard this before.
> Does anyone feel AUTOCLOSE needs to be set to True for
> Enterprise Manager/Agent/Jobs backups to be reliable?
> Please explain.
> Thanks
need to have AUTOCLOSE turned on for my SQL Server 2000
backups to be reliable. I have never heard this before.
Does anyone feel AUTOCLOSE needs to be set to True for
Enterprise Manager/Agent/Jobs backups to be reliable?
Please explain.
ThanksThat is complete rubbish. :-)
Your vendor apparently think that you do backup through nicking the database
files at the file system level...
Even if you did, autoclose is not, AFAIK, documented to work for this. I nev
er recommend to do backup of the
database files, but if someone would do that, you need to shutdown SQL Serve
r.
(And convincing me of the opposite requires a BOL topic or a KB article ;-).
Such may very well exists, and I
may just have missed it, of course.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Johnny Kennedy" <jkennedy@.pcmh.com> wrote in message news:12a7a01c411b6$f56bf6b0$a401280a@.
phx.gbl...
> It has been suggested to me by one of my vendors that I
> need to have AUTOCLOSE turned on for my SQL Server 2000
> backups to be reliable. I have never heard this before.
> Does anyone feel AUTOCLOSE needs to be set to True for
> Enterprise Manager/Agent/Jobs backups to be reliable?
> Please explain.
> Thanks
AUTOCLOSE
What does autoclose do ?
And why Starting up database message is repeated when autoclose is
true?"AMBROSE" <jeyaprabhu.j@.gmail.com> wrote in message
news:1172635162.065647.3570@.z35g2000cwz.googlegroups.com...
> What does autoclose do ?
> And why Starting up database message is repeated when autoclose is
> true?
>
If SQL Server doesn't detect usage of the said database in some interval
(can't recall what it is) it "closes" the database.
Then next time there's access, it reopens it, hence the startup up messages.
I recommend keeping autoclose turned OFF.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||This database option "closes" the database file whenever the last users
disconnects from the database. The resources are freed up,
but when a new user connects to the server, the database is reopened and
takes up some resources. So it is good to make this option turned
off for all databases in a production server.
Thanks
Hari
"AMBROSE" <jeyaprabhu.j@.gmail.com> wrote in message
news:1172635162.065647.3570@.z35g2000cwz.googlegroups.com...
> What does autoclose do ?
> And why Starting up database message is repeated when autoclose is
> true?
>|||On Feb 28, 9:56 am, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:[vbcol=seagreen]
> This database option "closes" the database file whenever the last users
> disconnects from the database. The resources are freed up,
> but when a new user connects to the server, the database is reopened and
> takes up some resources. So it is good to make this option turned
> off for all databases in a production server.
> Thanks
> Hari
> "AMBROSE" <jeyaprabh...@.gmail.com> wrote in message
> news:1172635162.065647.3570@.z35g2000cwz.googlegroups.com...
>
>
Will autoclose option set to true create any login problem?|||> Will autoclose option set to true create any login problem?
It shouldn't and I haven't heard of such.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AMBROSE" <jeyaprabhu.j@.gmail.com> wrote in message
news:1172639991.878459.94420@.h3g2000cwc.googlegroups.com...
> On Feb 28, 9:56 am, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Will autoclose option set to true create any login problem?
>|||Hi
"AMBROSE" wrote:
> On Feb 28, 9:56 am, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Will autoclose option set to true create any login problem?
>
If you have Anti-Virus software scanning your database files there can be
problems opening the database if a scan is occurring.
You may want to read http://support.microsoft.com/default.aspx/kb/309422
John|||On Feb 27, 11:19 pm, "AMBROSE" <jeyaprabh...@.gmail.com> wrote:
> Will autoclose option set to true create any login problem?
If the "default" database for a given login is "closed", and something
prevents it from being "opened", that login will fail.|||> Will autoclose option set to true create any login problem?
Sure, in addition to the other mentioned problems, if it takes a long time
to re-open the database, the login attempt may time out.
I strongly recommend NOT using autoclose.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
And why Starting up database message is repeated when autoclose is
true?"AMBROSE" <jeyaprabhu.j@.gmail.com> wrote in message
news:1172635162.065647.3570@.z35g2000cwz.googlegroups.com...
> What does autoclose do ?
> And why Starting up database message is repeated when autoclose is
> true?
>
If SQL Server doesn't detect usage of the said database in some interval
(can't recall what it is) it "closes" the database.
Then next time there's access, it reopens it, hence the startup up messages.
I recommend keeping autoclose turned OFF.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||This database option "closes" the database file whenever the last users
disconnects from the database. The resources are freed up,
but when a new user connects to the server, the database is reopened and
takes up some resources. So it is good to make this option turned
off for all databases in a production server.
Thanks
Hari
"AMBROSE" <jeyaprabhu.j@.gmail.com> wrote in message
news:1172635162.065647.3570@.z35g2000cwz.googlegroups.com...
> What does autoclose do ?
> And why Starting up database message is repeated when autoclose is
> true?
>|||On Feb 28, 9:56 am, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:[vbcol=seagreen]
> This database option "closes" the database file whenever the last users
> disconnects from the database. The resources are freed up,
> but when a new user connects to the server, the database is reopened and
> takes up some resources. So it is good to make this option turned
> off for all databases in a production server.
> Thanks
> Hari
> "AMBROSE" <jeyaprabh...@.gmail.com> wrote in message
> news:1172635162.065647.3570@.z35g2000cwz.googlegroups.com...
>
>
Will autoclose option set to true create any login problem?|||> Will autoclose option set to true create any login problem?
It shouldn't and I haven't heard of such.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AMBROSE" <jeyaprabhu.j@.gmail.com> wrote in message
news:1172639991.878459.94420@.h3g2000cwc.googlegroups.com...
> On Feb 28, 9:56 am, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Will autoclose option set to true create any login problem?
>|||Hi
"AMBROSE" wrote:
> On Feb 28, 9:56 am, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Will autoclose option set to true create any login problem?
>
If you have Anti-Virus software scanning your database files there can be
problems opening the database if a scan is occurring.
You may want to read http://support.microsoft.com/default.aspx/kb/309422
John|||On Feb 27, 11:19 pm, "AMBROSE" <jeyaprabh...@.gmail.com> wrote:
> Will autoclose option set to true create any login problem?
If the "default" database for a given login is "closed", and something
prevents it from being "opened", that login will fail.|||> Will autoclose option set to true create any login problem?
Sure, in addition to the other mentioned problems, if it takes a long time
to re-open the database, the login attempt may time out.
I strongly recommend NOT using autoclose.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
Subscribe to:
Posts (Atom)