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.