DB option drawbacks...

Hi... just wanted to gather some thoughts on the drawbacks
of setting the 'Auto_Shrink', 'Auto_Close',
and 'Auto_Create/Update_Statistics' db options.
Thanks.
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:0aac01c4ee94$be862790$a601280a@.phx.gbl...
> Hi... just wanted to gather some thoughts on the drawbacks
> of setting the 'Auto_Shrink', 'Auto_Close',
Don't do either.
Auto_shrink generally is a bad idea since if you're using it, it generally
means your DB is ping-ponging back and forth in size. This can lead to disk
level fragmentation.
As for auto_close, generally a really bad idea as it can kill performance.
("opening" a DB can take a few seconds.)

> and 'Auto_Create/Update_Statistics' db options.
>
Generally a good idea.

> Thanks.
|||Most people leave auto update/create statistics on ( that is the MS
recommendation.)
Most people leave autoshrink off... However this is really a trade off
between self maintenance and data file size...
If you a doing a database for a mom and pop shop where disk space is at a
premium ( more important than transient performance degradataion.) I might
choose auto shrink on.. But for large companies with a DBA staff, the DBA
should schedule the shrink if and when necessary during off hours.
Regarding auto close. SQL uses memory for meta data about each database
after someone uses the database, even if there is no one using the database
currently.. The initial load of metadata can take a few seconds. After
that, to leave and return to the database is fast because the metadata is
retained and does not have to be reloaded... However, if you are on a
machine with serious memory shortcomings ( like developing on your laptop).
you might want to set autoclose ON.. When you leave the database the memory
for metadata is released. <however if you re-enter the database you will
have to wait while the metadata is reloaded>
Hope this helps.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:0aac01c4ee94$be862790$a601280a@.phx.gbl...
> Hi... just wanted to gather some thoughts on the drawbacks
> of setting the 'Auto_Shrink', 'Auto_Close',
> and 'Auto_Create/Update_Statistics' db options.
> Thanks.

DB option drawbacks...

Hi... just wanted to gather some thoughts on the drawbacks
of setting the 'Auto_Shrink', 'Auto_Close',
and 'Auto_Create/Update_Statistics' db options.
Thanks."Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:0aac01c4ee94$be862790$a601280a@.phx.gbl...
> Hi... just wanted to gather some thoughts on the drawbacks
> of setting the 'Auto_Shrink', 'Auto_Close',
Don't do either.
Auto_shrink generally is a bad idea since if you're using it, it generally
means your DB is ping-ponging back and forth in size. This can lead to disk
level fragmentation.
As for auto_close, generally a really bad idea as it can kill performance.
("opening" a DB can take a few seconds.)

> and 'Auto_Create/Update_Statistics' db options.
>
Generally a good idea.

> Thanks.|||Most people leave auto update/create statistics on ( that is the MS
recommendation.)
Most people leave autoshrink off... However this is really a trade off
between self maintenance and data file size...
If you a doing a database for a mom and pop shop where disk space is at a
premium ( more important than transient performance degradataion.) I might
choose auto shrink on.. But for large companies with a DBA staff, the DBA
should schedule the shrink if and when necessary during off hours.
Regarding auto close. SQL uses memory for meta data about each database
after someone uses the database, even if there is no one using the database
currently.. The initial load of metadata can take a few seconds. After
that, to leave and return to the database is fast because the metadata is
retained and does not have to be reloaded... However, if you are on a
machine with serious memory shortcomings ( like developing on your laptop).
you might want to set autoclose ON.. When you leave the database the memory
for metadata is released. <however if you re-enter the database you will
have to wait while the metadata is reloaded>
Hope this helps.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:0aac01c4ee94$be862790$a601280a@.phx.gbl...
> Hi... just wanted to gather some thoughts on the drawbacks
> of setting the 'Auto_Shrink', 'Auto_Close',
> and 'Auto_Create/Update_Statistics' db options.
> Thanks.

DB option drawbacks...

Hi... just wanted to gather some thoughts on the drawbacks
of setting the 'Auto_Shrink', 'Auto_Close',
and 'Auto_Create/Update_Statistics' db options.
Thanks."Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:0aac01c4ee94$be862790$a601280a@.phx.gbl...
> Hi... just wanted to gather some thoughts on the drawbacks
> of setting the 'Auto_Shrink', 'Auto_Close',
Don't do either.
Auto_shrink generally is a bad idea since if you're using it, it generally
means your DB is ping-ponging back and forth in size. This can lead to disk
level fragmentation.
As for auto_close, generally a really bad idea as it can kill performance.
("opening" a DB can take a few seconds.)
> and 'Auto_Create/Update_Statistics' db options.
>
Generally a good idea.
> Thanks.|||Most people leave auto update/create statistics on ( that is the MS
recommendation.)
Most people leave autoshrink off... However this is really a trade off
between self maintenance and data file size...
If you a doing a database for a mom and pop shop where disk space is at a
premium ( more important than transient performance degradataion.) I might
choose auto shrink on.. But for large companies with a DBA staff, the DBA
should schedule the shrink if and when necessary during off hours.
Regarding auto close. SQL uses memory for meta data about each database
after someone uses the database, even if there is no one using the database
currently.. The initial load of metadata can take a few seconds. After
that, to leave and return to the database is fast because the metadata is
retained and does not have to be reloaded... However, if you are on a
machine with serious memory shortcomings ( like developing on your laptop).
you might want to set autoclose ON.. When you leave the database the memory
for metadata is released. <however if you re-enter the database you will
have to wait while the metadata is reloaded>
Hope this helps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:0aac01c4ee94$be862790$a601280a@.phx.gbl...
> Hi... just wanted to gather some thoughts on the drawbacks
> of setting the 'Auto_Shrink', 'Auto_Close',
> and 'Auto_Create/Update_Statistics' db options.
> Thanks.

DB Maintenance Plan

What are your thoughts on using the DB maintenance plan wizard? Any pros or cons?
Thanks in advance
Maintains plans make it easy to perform maintenance. If you want ultimate
control over your backups, index reorg, shrinking of files, etc then you
might not want to use maintenance plan since you don't have complete
control. I don't use maintenance plans.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Niles" <anonymous@.discussions.microsoft.com> wrote in message
news:8664CC9E-CA45-4CF7-B1DE-91CA0A380039@.microsoft.com...
> What are your thoughts on using the DB maintenance plan wizard? Any pros
or cons?
> Thanks in advance
|||Anything you can do with the MP you can do with a few lines of code. There
are even some things that you can't do with the MP. For instance you can't
issue Diff backups or Reindex tables with calculated columns, Indexed views
etc.
Andrew J. Kelly
SQL Server MVP
"Niles" <anonymous@.discussions.microsoft.com> wrote in message
news:8664CC9E-CA45-4CF7-B1DE-91CA0A380039@.microsoft.com...
> What are your thoughts on using the DB maintenance plan wizard? Any pros
or cons?
> Thanks in advance

DB Maintenance Plan

What are your thoughts on using the DB maintenance plan wizard? Any pros or
cons?
Thanks in advanceMaintains plans make it easy to perform maintenance. If you want ultimate
control over your backups, index reorg, shrinking of files, etc then you
might not want to use maintenance plan since you don't have complete
control. I don't use maintenance plans.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Niles" <anonymous@.discussions.microsoft.com> wrote in message
news:8664CC9E-CA45-4CF7-B1DE-91CA0A380039@.microsoft.com...
> What are your thoughts on using the DB maintenance plan wizard? Any pros
or cons?
> Thanks in advance|||Anything you can do with the MP you can do with a few lines of code. There
are even some things that you can't do with the MP. For instance you can't
issue Diff backups or Reindex tables with calculated columns, Indexed views
etc.
Andrew J. Kelly
SQL Server MVP
"Niles" <anonymous@.discussions.microsoft.com> wrote in message
news:8664CC9E-CA45-4CF7-B1DE-91CA0A380039@.microsoft.com...
> What are your thoughts on using the DB maintenance plan wizard? Any pros
or cons?
> Thanks in advance