Cranky Series: Don’t Enable Autoshrink. Ever.

10

August 10, 2012 by Mike Hillwig

This is the third post in my series How Not to be a Cranky DBA.

Autoshrink is bad. Don’t enable it. Ever. Seriously, I could stop this post right here.

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.

10 thoughts on “Cranky Series: Don’t Enable Autoshrink. Ever.

  1. Brent Ozar says:

    You’ve got it all wrong. Autoshrink means that the database is biodegradable. It’s a good thing! You DO like the environment, don’t you? You don’t want those big databases cluttering up disk space in landfills, do you, you selfish pig? 😉

  2. […] people have documented this before, myself included. Microsoft is saying it. Please disable AUTO_SHRINK. Now. […]

  3. Carmen says:

    Ah yes um that was me and my fiance. She magaend to punt us into a tree whilst I magaend to punt into others and cause general mayhem. We got as far as the first bridge and back about a 200ft round trip in one hour!(@robinsasson)

  4. I love reading these articles because they’re short but informative.

  5. That’s an inventive answer to an interesting question

  6. Bonjour,Je serais à New York du 14 au 23 avril, savez vous si il est possible de tomber sur une date du tournage de gossip girl, ou la saison risque d’être finis de tourner ?Merci

  7. Are your pets green????? Check out my channel for some green tips for pets…..and stay tuned for my blog to learn even more… There are millions of companion animals whose parents can help them go green also…

  8. Pretty great post. I simply stumbled upon your weblog and wished to mention that I have truly loved browsing your blog posts. In any case I will be subscribing on your rss feed and I hope you write again very soon!

  9. If we are talking about applications which cost money, I suggest you to use the sites approved by Apple. Otherwise there are many websites with big collection of free apps not only for iphone and ipad. Those 12 look good too.

  10. May I just say (since I don't intend to see Skyfall) that the illustrated British brain cracked me up (especially the beer and tea sharing space in the esophagus, and both directed up)?

Leave a Reply

Your email address will not be published. Required fields are marked *