Is it safe to have SQL Server auto-shrink turned on?

(I originally asked as a regular question but then found out the correct method – thanks BrentO)

No, never.

I’ve come across this several times now on ServerFault and want to reach a nice wide audience with some good advice. If people frown on this way of doing things, downvote and I’ll remove this gladly.

Auto-shrink is a very common database setting to have enabled. It seems like a good idea – remove the extra space from the database. There are lots of ‘involuntary DBAs’ out there (think TFS, SharePoint, BizTalk, or just regular old SQL Server) who may not know that auto-shrink is positively evil.

While at Microsoft I used to own the SQL Server Storage Engine and tried to remove the auto-shrink feature, but it had to stay for backwards compatibility.

Why is auto-shrink so bad?

The database is likely to just grow again, so why shrink it?

1. Shrink-grow-shrink-grow causes file-system level fragmentation and takes lots of resources.
2. You can’t control when it kicks-in (even though it’s regular-ish)
3. It uses lots of resources. Moving pages around in the database takes CPU, lots of IO, and generates lots of transaction log.
4. Here’s the real kicker: data file shrink (whether auto- or not) causes massive index fragmentation, which leads to poor performance.

I did a blog post a while back that has an example SQL script that shows the problems it causes and explains in a bit more detail. See Auto-shrink – turn it OFF! (no advertising or junk like that on my blog). Don’t get this confused with shrinking the log file, which is useful and necessary on occasion.

So do yourselves a favor – look in your database settings and turn off auto-shrink. You should also not have shrink in your maintenance plans, for exactly the same reason. Spread the word to your colleagues.

Edit: I should add this, reminded by the second answer – there’s common misconception that interrupting a shrink operation can cause corruption. No it won’t. I used to own the shrink code in SQL Server – it rolls back the current page move that it’s doing if interrupted.

Hope this helps!