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

Comments (5)
  1. hfrmobile says:

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

  2. Aaron says:

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

  3. araniya bipin says:

    " 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

  4. jorge guateque says:

    gracias.  estoy aprendiendo sql y esto es sumamente interesante

  5. Mr. SQL says:

    As of today, there are still many large and foolish software vendors that will set this and even have a maintenance job that does it nightly. Makes managing a large environment difficult when uneducated people make a SWAG at a setting without research.

Comments are closed.

Skip to main content