SQL Server Best Practices: Auto-Shrink Should Be Off

SQL Server Best Practices: Auto-Shrink Should Be Off

Rate This
  • Comments 3

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

Leave a Comment
  • Please add 4 and 1 and type the answer here:
  • Post
  • "I never shrink my databases unless I have a huge deletion of data, and I know that the data won't come back."

    --> I agree ;-) In my current project we have such a situation one time / year.

  • Why not leave it on, and schedule a task to rebuild your indexes on a regular basis?

  • " i don't want my data come back after deletion operation ...? is that shrink operation work.. Hello hfrmobile can you explain me how can you do this thing for deleting of data...

    ...please help me i have such a problem in my current project

    .. my email id: araniyabipin@gmail.com / lotus.websolution@gmail.com

Page 1 of 1 (3 items)