Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

7 Answers 7

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

share|improve this answer
    
Is there a way you could re-index right after a shrink? –  Lance Roberts Jun 10 '09 at 0:09
2  
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

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.

share|improve this answer
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 '09 at 23:23

Of course Paul is right.

See all dbs and their autoshrink setting. If you have alot of databases, one will sneak in.

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

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

share|improve this answer
    
sys.databases has a field is_auto_shrink (and is_auto-close, is_auto_update_stats, etc) –  Paul Randal Jun 11 '09 at 14:11

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

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

share|improve this answer
    
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 '09 at 5:17
2  
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 '09 at 5:19

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.
share|improve this answer

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

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.