This is kind of a follow up to a post I published last week regarding autogrowth. A forum poster said they had turned on AutoGrow and they were using autogrow as way of managing the growth of their databases – No! This post titled “Should I have AutoGrowth enabled on my SQL Server Database?” addresses why you should have autogrow enabled and why you should only use it as a fail back. Instead monitor and grow your databases manually.
The same forum poster also said “that just in case the database grew too much and needed space released back to Operating System, he had enabled AutoShrink.” No!
Why?
This is the short answer: Shrinking databases is generally a bad thing to do it can will cause fragmentation in your indexes, which in turn can be bad for performance. Even though you may *occasionally* need to shrink your database, Shrinking a database should not be part of your regular database maintenance routines. If shrink is used at all it should be used very very infrequently. Letting SQL Server shrink your database for you automatically, when *it* thinks it needs to done and with no regard for the time of day is much much worse for you as a DBA. You may find that this process kicks in the middle of day when your system is at its busiest taking up valuable resources as well as completely fragmenting your indexes.
Addition Reading
Paul Randal (@PaulRandal) has a blog post discussing the very same topic http://www.sqlskills.com/blogs/paul/post/Auto-shrink-e28093-turn-it-OFF!.aspx he has a great example that demonstrates how shrink can affect index fragmentation levels, so if you are looking for proof head over to Paul’s post above
