SQL Server Best Practices: Auto-Shrink Should Be Off

SQL Server is one of the easiest databases to maintain because of all of the automatic settings it has, but as I mentioned with Auto-Close, some of them should be left off. The Auto-Shrink setting is another.

That might surprise a few people. You might think from the name of this operation, that you would want your databases to automatically reclaim any space they take whenever they can. And if SQL Server will handle that for you, all the more wonderful. But in fact, shrinking a database can cause your Indexes to be fragmented, especially if it happens a lot. I never shrink my databases unless I have a huge deletion of data, and I know that the data won't come back. That's a pretty rare event, and when it does happen I run the shrink operation manually and rebuild my indexes after.

My friend Paul Randall has a great explanation of why this happens, and another way to reclaim that space without running shrink at all: http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx