Auto Shrink on Production Data base - Mukut

Hi all,

I need your kind suggestions on this issue.

We have a single server machine where more than 5 seperate production
databases are installed. For all the databases, autoshrink was made to
turn on. Now, we are going out of the space at the server frequently.

In past, several time I have found in this forum that "Autoshrink is
not a good option for production database".

Now I request you to provide detail suggestion on this...should I make
Autoshrink turned off? If yes, then why(pls describe in detail)?

What should be the ideal and right way of handle file size in the
production box?

Thanks,
Mukut

Auto Shrink on Production Data base - SQL Menace

Read "Why you want to be restrictive with shrink of database files "
http://www.karaszi.com/SQLServer/info_dont_shrink.asp



Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx

Auto Shrink on Production Data base - Andrew J. Kelly

A production db should almost never have AutoSrink turned on. See Tibors
article for details.

--
Andrew J. Kelly SQL MVP

Auto Shrink on Production Data base - Roy Harvey

What you have read is correct, AUTOSHRINK should not be used on
production databases.  Shrinking the files means that they have to
grow again when more data is inserted.  One problem is that growing a
database file is expensive in terms of system resources and causes the
insert or update command that triggers the growth to wait.  Another
problem is that growing in small increments fragments the physical
files.

The right way to handle file size is to estimate the size needed, add
a comfortable amount extra for "elbow room", and set that as the
minimum size.  Then regularly monitor the free space.  When it starts
to run short expand the file size by enough that you will not have to
expand it again any time soon.  By doing this before space runs out
nobody is waiting for it to complete.  I think that you should also
specify autogrow to prevent running out of space when the monitoring
is not done adequately, but space should be increased before any
automatic growth.

If you are running out of space with autogrow on then you need more
disk.  One thing I figured out a long time ago is that managing SQL
Server without plenty of extra disk space wastes enormous amounts of
DBA time.  Invest in plenty of extra disk space.

Roy Harvey
Beacon Falls, CT

Auto Shrink on Production Data base - Mukut

Thanks you all for your valuable suggestions. I have read the Tibor's
article and really impressed by it.

Thanks you all once again.