Shrinking SQL Server Data Files – Best Practices, and Why It Sucks

dbcc-shrinkfileI’ve gotten a few questions about shrinking SQL Server data files lately. What’s the best way to get shrink to run? And why might it fail in some cases?

Traditionally, every time you ask a DBA how to make shrinking suck less, they start ranting how shrinking is bad and you just shouldn’t do it. Sometimes it sounds kinda angry.

What people are trying to say is that shrinking data files is generally slow, frustrating, and painful for you.

Shrinking data files sucks, and you don’t really have many ways to make it suck less

Here’s what you’re up against, when it comes to shrinking:

  • Shrinking can cause blocking while it runs. Here’s a post I wrote a while back with a demo script to reproduce the blocking.
  • Shrinking may stop running and not tell you why.
    • In one case, DBCC SHRINKFILE was stopping because it was hitting a deadlock and being declared the deadlock victim. That message didn’t show in the messages window for the session running SHRINKFILE though! Fun? (Not really.)
  • Shrinking fragments your indexes – if the indexes are large and you run a rebuild command on them, you may just regrow the data files anyway.
  • If shrink has to move around LOB data, it’s super slow. Paul Randal explains why here.
    • Note that you might hit this even if the data you deleted doesn’t contain LOB data. If there are LOB pages closer to the end of the file, SQL will pick up those pages and move them towards the beginning of the file.

It’s not your fault that shrinking has all this baggage. You just want to have a smaller database. But these things are why you generally want to avoid shrinking whenever possible.

data-grooming

Most senior DBAs proactively keep free space in the database and regularly archive data

Planning beats shrinking, every day.

Data growth and grooming should never be dictated by disk space. The data is more important in than the disk space!

  • Regularly clean up data that’s truly no longer needed.
    • If you can have a nightly process that removes old data in small batches, rather than doing it once a quarter, that’s usually better for performance. It varies depending on when your database is in use, and whether your data removal process might block users.
    • Sometimes it’s desirable for a business to remove data which is no longer needed quickly for legal reasons. This varies a lot by industry, though.
  • Measure data growth regularly, and start to estimate how much data is likely to grow in the future
  • Keep free space in the database to allow for data growth without file growths.
    • This means proactively requesting more disk space and proactively growing the files. You can do it quarterly or once or twice a year — whatever works best for you, but set up reminders and don’t skip it.

Charting data growth and proactively managing the space ahead of time keeps you out of reactive “shrink mode”.

It also makes you look good to your management to be on top of this!

Sometimes you do have to shrink data files

Sure, shrinking stinks, and you avoid it when you can. But sometimes you have a one-time archive of a lot of data. You know that you aren’t going to reuse the space in the next year, and it’s enough space that shrinking will make restoring the database elsewhere easier.

In those cases:

  • Consider workarounds. Sometimes it’s less work to import the remaining data into a new database during an outage, depending on how much you’re removing and how much is left.
    • This could also allow you to carefully plan a nice filegroup and file layout in the new database.
  • Identify low use times you can run the shrink.
  • Use DBCC SHRINKFILE and set a specific, targeted size for the file you’re shrinking.
  • Watch your backup jobs, and make sure they’re succeeding and not taking way longer than normal.
  • Plan to leave empty space in your data files to allow for growth over the next year, and to allow index rebuilds to work if needed after the shrink is done. No need to over-shrink and then re-grow.
  • Watch for blocking if you don’t have an outage window
  • If you need to know how long the shrink is going to take, restore a copy of the database elsewhere and test ahead of time to get an idea — but it’s still going to be a rough estimate.
    • Knowing how much LOB data it’s going to have to move is really hard from the outside. Blocking makes a different as well, as does hardware and disk speed, so it’s really tough to estimate.
  • Make sure you’re running DBCC CHECKDB regularly. Because you should be doing that! (No, shrinking shouldn’t particularly cause corruption, but a little caution ain’t bad, either.)

And maybe find a show to binge-watch on Netflix while your shrink is running. Something that just takes a couple of brain cells to watch, and which you wouldn’t mind pausing if things get funky.

Previous Post
What’s Adaptive Query Processing? (Dear SQL DBA Episode 21)
Next Post
Filtered Indexes: Rowstore vs Nonclustered Columnstore

Related Posts

11 Comments. Leave new

All great points Kendra. Thank you for this post.

Reply

Interesting post Kendra, Thank you so much for your effort.

Reply

Thank you for the post, Kendra

Reply

Hi Kendra,

nice blog post! Could you elaborate a bit more on shrinking log files in specific? Is this less bad than shrinking data files?

Thanks

Martin

Reply

    Hi Martin,

    Great question! The log is completely different from data files. There is an excellent article on managing transaction log size on SQL Server Central in their “stairway” on Transaction Log Management: http://www.sqlservercentral.com/articles/Stairway+Series/94579/

    It goes into a lot of detail to explain how the log works, why it might grow excessively, and how to prevent it from growing. The main issue that I see with folks and shrinking log files is that they get into a pattern of regularly re-shrinking it and don’t understand why it grew. (Which ends up being wasted effort– no point in shriking it if it’s just going to re-grow.)

    The rest of the series is terrific as well.
    Kendra

    Reply

Hi Kendra, thanks for this post.

I am trying to shrink a large (3.6 TB) data file with only 0.5 TB used space, and running this command in chunks of 10 GB
(to be able to have the space released as soon as it becomes available):

DBCC SHRINKFILE (N’filename’, 3674854);
GO
DBCC SHRINKFILE (N’filename’, 3664854);
GO

The odd thing is that each iteration completes with no errors, but the file size remains the same.
I have checked for deadlocks in systemhealth, but there aren’t any.
This is a new dev env on which I have exclusive use at the moment, so no contention from competing processes.

Can you think of any reason why this might be happening?

This is a SQL-2016 instance on which I have updated the db from SQL-2012 to SQL-2016 by changing the comp level and running DBCC UPDATEUSAGE.

Any advice would be welcome.

Thank you,
Marios

Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu