45

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?

Paul Randal
  • 7,184
  • 1
  • 35
  • 45

7 Answers7

72

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

Paul Randal
  • 7,184
  • 1
  • 35
  • 45
  • Is there a way you could re-index right after a shrink? – Lance Roberts Jun 10 '09 at 00:09
  • 4
    Not reindex because that will grow the file again (builds new index before dropping the old one), but doing an reorganize (either through my old DBCC INDEXDEFRAG or the new ALTER INDEX ... REORGANIZE) will sort out fragmentation, at the expense of more IO, cpu, logging... – Paul Randal Jun 10 '09 at 12:17
  • I noticed that after removing autoshrink the memory usage of the srrver is higher. – user193655 Feb 04 '16 at 14:33
4

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.

David Ferenczy Rogožan
  • 281
  • 2
  • 7
Sam
  • 1,990
  • 1
  • 14
  • 21
2

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.

David Spillett
  • 22,534
  • 42
  • 66
  • 2
    Shrinks shouldn't be scheduled - they should be really rare operations because of the problems they cause. I don't understand your comment about doing the shrink after the backup - the log records generated by the shrink operation will get picked up by the next transaction log backup regardless of when you do it or what other backups you take. Thanks – Paul Randal Jun 05 '09 at 23:23
1

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

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

  • Unfortunately that whitepaper is aimed at SharePoint installations only and actually has some errors in. I just spent time teaching the current SharePoint MCM class with the whitepaper's author, Bill Baer. – Paul Randal Jun 06 '09 at 05:17
  • 3
    A good general introduction to database maintenance is in my TechNet Magazine article on the subject Effective Database Maintenance - http://technet.microsoft.com/en-us/magazine/cc671165.aspx. – Paul Randal Jun 06 '09 at 05:19
1

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.
Carl C
  • 1,038
  • 3
  • 10
  • 19
1

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.)

SuperCoolMoss
  • 1,252
  • 11
  • 20
1

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