August 10, 2012 by Mike Hillwig
This is the third post in my series How Not to be a Cranky DBA.
But let me take it one step further: Paul says so.
This is the one time where “it depends” is not an appropriate answer. Don’t enable it. Ever. Yes, I said ever. This is the one exception to when I don’t trust people who say you should ALWAYS or NEVER do something.
See that car driving off a cliff? That’s exactly what you’re doing when you enable autoshrink. I might argue that driving off the cliff might be less painful.
Autoshrink has this nasty habit of rearing its ugly head at the worst possible time. In my case, it was at 2:00 PM on a Tuesday afternoon at the end of the month while our finance department was trying to close the books. We were down for two hours. I looked incompetent.
I can only come up with one potential reason why autoshrink might be okay, and that’s when a vendor refuses to support your installation without it. Yes, the vendor is wrong and you need to convince them of this. But I had a vendor once refuse to support us without autoshrink enabled. My words were not kind, and it took a lot of convincing them that the performance problems we were facing were due to bad application design instead of too much white space in a database.
The sad thing is that it has such a benign name. Autoshrink make it sound like you’re doing something good, like recovering disk space. Perhaps a better name would be “hidden performance killer that should have never been put on the GUI.”
Here is my challenge to Microsoft: If you’re not going to get rid of this bad little option, please remove it from the Management Studio GUI. That way, if someone who knows what they’re really doing needs to enable it, they could do it via T-SQL.
There are better ways to recover disk space, but ask yourself if it’s worth it. What you shrink is just going to grow again, and that will happen right after you rebuild your indexes because autoshrink has fragmented the hell out of them.
Dont’ believe me? Don’t believe Paul? Maybe you’ll believe Tom. But whatever you do, disable autoshrink.