Turn AUTO_SHRINK off!!

  • Article

This week's topic is data file shrinking. I've seen lots of mis-information in the last few weeks and I've had a bunch of questions about it. First up is auto-shrink.

In my opinion, this feature causes way more problems than it solves (in fact, I can't think of a single problem it solves) and should be removed from the product. Remember I'm talking about auto-shrink, not manual shrink. Post a comment or drop me a line if you think there's a scenario where it's required.

So why?

  1. The algorithms that shrink use are basically brute force. It starts at the end of the data file, picks up allocated pages and moves them as near to the front of the data file as it can, fixing up all the links as it goes. This is fine as long as there are no indexes involved - if there are, every time shrink moves a non-clustered index leaf-page, or a clustered index data-page, it's causing fragmentation. Yes, any shrink (apart from a TRUNCATEONLY) can cause fragmentation - this is not widely known, although every opportunity I get to explain this I do. I updated the Books Online for DBCC SHRINKDATABASE in SQL Server 2005 to call this out as a reason not to run shrink in general. So, although this affects all shrink operations, its still a very good reason not to run shrink automatically.
  2. You can't control when it kicks in. Although it doesn't have any effect like long-term blocking, it does take up a lot of resources, both IO and CPU. It also moves a lot of data through the buffer pool and so can cause hot pages to be pushed out to disk, slowing things down further. If the server is already pushing the limits of the IO subsystem, running shrink may push it over, causing long disk queue lengths and possibly IO timeouts.
  3. You can't control when it kicks in - it will start up every 30 minutes and try to shrink one of the databases that has AUTO_SHRINK turned on. 
  4. You're likely to get into a death-spiral of auto-grow then auto-shrink then auto-grow then auto-shrink... (in my experience, if someone is using auto-shrink, they're most likely using and relying on auto-grow too). An active database usually requires free space for normal operations - so if you take that free space away then the database just has to grow again. This is bad for several reasons:
  • Repeatedly shrinking and growing the data files will cause file-system level fragmentation, which can slow down performance
  • It wastes a huge amount of resources, basically running the shrink algorithm for no reason
  • Auto-grow itself can be bad, especially if you're using SQL Server 2000 (or don't have Instant File Initialization turned on - see this recent post from Kimberly's blog) where all allocations to the file being grown are blocked while the new portion of the file is being zero-initialized.

So - if that hasn't convinced you to make sure its turned off, I don't know what will. For databases created on SQL Server 2005, the AUTO_SHRINK option is off by default. You can use ALTER DATABASE yourdb SET AUTO_SHRINK OFF for all others, especially the MODEL database, so new databases don't inherit the setting unwittingly.

Next up - a look inside the algorithm for 2005 and how you may see the run-time of shrink increase unexpectedly, depending on your schema...