Home / server / Questions / 20909
In Process
Paul Randal
Asked: 2009-06-06 15:04:47 +0800 CST

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

  • 772

There are many SQL Server options that can be enabled for databases, and one of the most misunderstood ones is auto-shrink. Is it safe? If not, why not?

sql-server team-foundation-server sharepoint database

7 Answers

  1. (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!

    • 72

  2. Of course, Paul is right.

    See all DBs and their autoshrink setting. If you have a lot of databases, one will sneak in.

    sp_msforeachdb  @command1 = 'Select ''[?]'',DATABASEPROPERTYEX(''?'',''IsAutoShrink'')'

    Is this in the dmv's somewhere....I wonder.

    • 4

  3. It isn't "unsafe" - it won't damage anything.

    But it is not recommended for production environments where the database may decide to go off and start an expensive rearrangement exercise just before a pile of requests come in making those requests take longer to be served. You are much better off using scheduling shrink operations along with other maintenance operations such as backups (actually, after backups - it'll more from the transaction log that way). Or just not shrinking at all unless there is a growth problem - you can always setup a monitor to let you know when the unused allocated space grows beyond a certain ratio or fixed size.

    IIRC the option is off by default for all databases in all MSSQL editions except Express.

    • 2

  4. There is a whitepaper available on TechNet that explains SQL maintenance in more detail.

    http://technet.microsoft.com/en-us/library/cc262731.aspx

    • 1

  5. I've seen a SQL server with both Autogrow and Autoshrink enabled. This (relatively powerful) server was terribly slow, because all it did all day was shrink and grow the database files. Autoshrink can be useful, but I'd recommend two things:

    1. Turn Autoshrink off by default.
    2. Document your server configs, so you know where Autogrow and Autoshrink are enabled and where they're not.
    • 1

  6. The only time I've been forced to shrink a database was to refresh a copy on a test server with less disk space (insufficient to hold the production database).

    The production database's file(s) had generous free space, unfortunately you have to restore a database with the same file(s) sizes as you've backed it up with. So had no choice but to shrink production before backing it up. (The shrink took ages, lots of resource was consumed and the subsequent transaction log growth was problematic.)

    • 1

  7. Also check out this video tutorial....

    Watch Paul Randal demonstrate how shrink and auto-shrink can cause serious fragmentation problems for your database http://wtv.watchtechvideos.com/topic194.html

    • 1