A SQL Server DBA myth a day: (9/30) data file shrink does not affect performance

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.)

Myth #9: Data file shrink does not affect performance.

Hahahahahahahahahahahahahahaha! <snort>

<wipes tears from eyes, attempts to focus on laptop screen, cleans drool from keyboard>

FALSE

The only time a data file shrink won’t affect performance is if you use the WITH TRUNCATEONLY option and there’s free space at the end of file being shrunk.

Shrink affects performance while it’s running. It’s moving tons of data around, generating I/Os, fully logging everything it does, and burning CPU.

Shrink affects performance after it’s run. All that log has to be backed up, log shipped, database mirrored, scanned by transactional replication, and so on. And if the data file grows again, the new space has to be zeroed out again (unless you have instant file initialization enabled).

Worst of all, shrink causes massive index fragmentation – which sucks down performance of range scans.

Unfortunately there was never time for me to rewrite the shrink code (I didn’t write it like that in the first place) so we’re probably stuck with its massive suckiness (that’s a technical term) forever.

Check out this blog post where I go into more details and explain an alternative to using shrink: Why you should not shrink your data files and this one for how to manage your data files correctly: Importance of data file size management. And this one just because it’s cool (safe for work): TGIF Time Warp.

And remember:

  • Data file shrink is evil
  • Shrinkdatabase is evil-er
  • Auto-shrink is evil-est

Shrink – just say no. With proper education and effective counselling, we can rid the world of this terrible affliction once and for all.

Happy Friday!

12 thoughts on “A SQL Server DBA myth a day: (9/30) data file shrink does not affect performance

  1. Hi Paul…agreed that shrink file is evil and all, but I have a situation where my customer in a SQL Server 2008 has about 1TB database (just migrated from SQL Server 2005). Customer enables page level data compression which takes about 38hrs, and compresses impressively to about 350GB. We needed to reclaim space to do other things, like add another instance or use the space as needed. Shrinking the files one at a time took almost 6days to complete. This is unacceptable to my customer. What best practices do you have in this case, that I may share with my customer to speed this process up?

  2. Hi Tunji – shrinkdatabase isn’t any faster than individual shrinkfiles – as all a shrinkdatabase does is shink each file in file-ID sequence. Best thing to do would have been to create a new filegroup and compress the indexes of the table into the new filegroup – then you would have been able to very quickly shrink/truncate the old file(s). This procedure is describedd in the "Why you should shrink your data files" blog post.

  3. Hello Paul, have a quick question about the shrink process . Is there is any way i can release storage back to machine by shrinking the database ? i have cleaned ~2 million rows from database.

    i learned the shrink database hurts but any other way around it.

    Thanks

  4. We have 1 partitioned table of size 20 TB, we are purging the 10 TB worth of data by partition switching option. What will be the appropriate option to reclaim the disk space? What is the impact on users if we run DBCC SHRINKFILE option in peak hours?

    1. There isn’t a good option for that amount of disk space. Shrink will cause a drop in performance and huge amount of fragmentation and t-log usage. If you really don’t need the space, and you anticipate SQL Server using it in future in that database, I’d leave it alone. Otherwise shrinking or rebuilding all indexes into a new filegroup are your options – neither of which are good at your data size.

  5. hi,

    Its not good to shrink data file!! got it. what about transaction log file? can I shrink that or is it a BIG NO, NO too?

    Thanks & Kind Regards
    Neil

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.