Monday, 20 December 2010

Turn Off AutoShrink!

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!


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!.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

No comments:

Post a Comment

Featured post

OUT OF SUPPORT: SQL Server 2008 and SQL Server 2008 R2

Welcome to this post, on SQL Server 2008 and 2008 R2, both of these versions of SQL server will go out of  extended support with Microsoft t...