47

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?

Improve this question

7 Answers 7

Reset to default
74

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

Improve this answer
3
  • Is there a way you could re-index right after a shrink?
    – Lance Roberts
    Jun 10, 2009 at 0: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, 2009 at 12:17
  • I noticed that after removing autoshrink the memory usage of the srrver is higher.
    – user193655
    Feb 4, 2016 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.

Improve this answer
2
  • 2
    sys.databases has a field is_auto_shrink (and is_auto-close, is_auto_update_stats, etc)
    – Paul Randal
    Jun 11, 2009 at 14:11
  • wonderful i m googling : how did i have a report helping us to know which dbs configured as autoshrink and your code running good and generate good report from all db for us
    – saber tabatabaee yazdi
    Feb 14, 2017 at 16:02
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.

Improve this answer
4
  • 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 5, 2009 at 23:23
  • Thank you @PaulRandal, so why AUTO_SHRINK exists in the first place?
    – Francesco Mantovani
    Jan 18 at 10:27
  • 1
    @FrancescoMantovani - likely because people wanted it, whether it was a good idea or not! Adn answering the old comment about shrink-after-backup, I was meaning the result will be smaller because the backups will mean some log pages can now be discarded that would otherwise have to moved by the shrink process. The page moves in the data files will create more log pages for the next backup to deal with, but that will happen whether you shrink before or after the backup.
    – David Spillett
    Jan 18 at 11:51
  • @FrancescoMantovani it's been there since the dawn of time basically
    – Paul Randal
    Jan 21 at 19:28
1

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

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

Improve this answer
2
  • 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 6, 2009 at 5:17
  • 3
    A good general introduction to database maintenance is in my TechNet Magazine article on the subject Effective Database Maintenance - technet.microsoft.com/en-us/magazine/cc671165.aspx.
    – Paul Randal
    Jun 6, 2009 at 5: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.
Improve this answer
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.)

Improve this answer
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

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .