What’s So Bad About Shrinking Databases with DBCC SHRINKDATABASE?

You read a lot of advice that says you shouldn’t shrink databases, but…why?

To demonstrate, I’m going to:

  1. Create a database
  2. Create a ~1GB table with 5M rows in it
  3. Put a clustered index on it, and check its fragmentation
  4. Look at my database’s empty space
  5. Shrink the database to get rid of the empty space
  6. And then see what happens next.

Here’s steps 1-3:

And here’s the output – I’ve taken the liberty of rearranging the output columns a little to make the screenshot easier to read on the web:

Near zero fragmentation – that’s good

We have 0.01% fragmentation, and 99.7% of each 8KB page is packed in solid with data. That’s great! When we scan this table, the pages will be in order, and chock full of tasty data, making for a quick scan.

Now, how much empty space do we have?

Let’s use this DBA.StackExchange.com query to check database empty space:

The results:

Our data file is 51% empty!

Well, that’s not good! Our 2,504MB data file has 1,277MB of free space in it, so it’s 51% empty! So let’s say we want to reclaim that space by shrinking the data file back down:

Let’s use DBCC SHRINKDATABASE to reclaim the empty space.

Run this command:

And it’ll reorganize the pages in the WorldOfHurt to leave just 1% free space. (You could even go with 0% if you want.) Then rerun the above free-space query again to see how the shrink worked:

Free space after the shrink

Woohoo! The data file is now down to 1,239MB, and only has 0.98% free space left. The log file is down to 24MB, too. All wine and roses, right?

But now check fragmentation.

Using the same query from earlier:

And the results:

After the shrink, 98% fragmented.

Suddenly, we have a new problem: our table is 98% fragmented. To do its work, SHRINKDATABASE goes to the end of the file, picks up pages there, and moves them to the first open available spaces in our data file. Remember how our object used to be perfectly in order? Well, now it’s in reverse order because SQL Server took the stuff at the end and put it at the beginning.

So what do you do to fix this? I’m going to hazard a guess that you have a nightly job set up to reorganize or rebuild indexes when they get heavily fragmented. In this case, with 98% fragmentation, I bet you’re going to want to rebuild that index.

Guess what happens when you rebuild the index?

SQL Server needs enough empty space in the database to build an entirely new copy of the index, so it’s going to:

  • Grow the data file out
  • Use that space to build the new copy of our index
  • Drop the old copy of our index, leaving a bunch of unused space in the file

Let’s prove it – rebuild the index and check fragmentation:

Our index is now perfectly defragmented:

Perfectly defragmented

But we’re right back to having a bunch of free space in the database:

Back to 1.2GB of empty space in the data file

Shrinking databases and rebuilding indexes is a vicious cycle.

You have high fragmentation, so you rebuild your indexes.

Which leaves a lot of empty space around, so you shrink your database.

Which causes high fragmentation, so you rebuild your indexes, which grows the databases right back out and leaves empty space again, and the cycle keeps perpetuating itself.

Break the cycle. Stop doing things that cause performance problems rather than fixing ’em. If your databases have some empty space in ’em, that’s fine – SQL Server will probably need that space again for regular operations like index rebuilds.

If you still think you want to shrink a database, check out how to shrink a database in 4 easy steps.

Erik Says: Sure, you can tell your rebuild to sort in tempdb, but considering one of our most popular posts is about shrinking tempdb, you’re not doing much better in that department either.

Previous Post
How Personally Identifiable Information Gets Around
Next Post
Heaps, Deletes, and Optimistic Isolation Levels

25 Comments. Leave new

  • I completely agree that regularly shrinking databases (or to be more specific, data files) is a very bad thing. It is very resource intensive and it creates severe index fragmentation. We’ll leave aside your previous guidance that index fragmentation does matter at all and that all index maintenance is a counter-productive waste of time.

    One valid reason to specifically shrink a data file would be if you had deleted a large amount of data that was not coming back any time soon (or ever) and/or you had dropped many unused large indexes, such that you had a quite large amount of free spaces in your data file(s).

    If this were the case, and if you were low on disk space on the LUNs where the data file(s) were located (and you could not easily grow the LUNs), then I might want to go ahead and shrink the data file(s) with DBCC SHRINKFILE commands, as a one-time operation.

    This would still be resource intensive, and would still cause severe index fragmentation, but I would have less concurrent impact to system than using DBCC SHRINKDATABASE (I would shrink the log file later, in a separate operation).

    After the DBCC SHRINKFILE operations were done, I would use ALTER INDEX REORGANIZE to reduce the index fragmentation without growing the data file(s) again. Simply using REORGANIZE rather than REBUILD avoids the vicious circle of shrinking and growing that your demo illustrates.

    ALTER INDEX IX ON dbo.Messages REORGANIZE;

    Finally, if necessary, I would shrink the log file, and then explicitly grow it in relatively large chunks to my desired size, to keep the VLF count low.

    So, I would never use DBCC SHRINKDATABASE. Instead, I would use DBCC SHRINKFILE only in special circumstances, and in a targeted manner, never as part of some automated, nightly process.

    Reply
  • Jean-Luc Lecomte
    December 29, 2017 11:52 am

    I am a fervent reader of your posts (as well as an avid viewer of your Office Hours, a regular user of sp_Blitz* and an accidental DBA who graduated with your free 6-months training program), so I guess it is time to thank you for all your contributions.

    This post is a perfect example of your skills at demonstrating how SQL Server “thinks”. I can’t wait to reproduce these steps on my test environment.

    A very Happy 2018 to the team!

    Reply
  • It is amazing to me, how many times I have been asked in interviews “How would you shrink a database” and my answer is ” I don’t know because I would never do it.” continue to explain why i would not shrink data files or databases. Then I hear crickets…

    Reply
    • I got hired for saying I have shrink-rebuild indexes tasks as part of my maintenance plan. What I didn’t say is I have them disabled with a link to this article in the description.

      Reply
  • Why did they build it that way?

    At some point, someone at Microsoft said “let’s make it go to the end of the file, picks up pages there, and moves them to the first open available spaces in our data file.” And someone else thought it was a good idea.

    Is (or was) there a case where DBCC SHRINKDATABASE is the right thing to do?

    Reply
    • James – back in the day when disk space truly was expensive, shrinking was much more important. These days, I see people wildly focused on shrinking a 10GB database down to 8GB, and at the same time wildly focused on fragmentation – two issues that just won’t matter for performance on databases of that size – and then doing the cycle shown in the post.

      If you have a 1TB database, and you truncate 500GB worth of tables that were supposed to be deleted daily, and you’ll never need that 500GB of free space, then sure, start shrinking that data file. (But like Glenn writes in his comment – only shrink the specific objects that require it.)

      Reply
  • Given you stance on fragmentation in indexes doesn’t matter (I think that’s been your point recently on your blog), then the argument to not shrink files because of fragmentation is moot isn’t it? If the only downside of shrinking files is fragmentation, and fragmentation doesn’t matter, then I might as well shrink files right?

    Is this something that you frequently see people doing?

    Have a great new year

    Reply
    • Greg – yep, I frequently see people doing it, and here’s the problem: you’re inflating your log backup sizes, data sent to the mirrors/AG replicas, slowing down file growths, and slowing down end user transactions while all this rebuild/shrink/rebuild/shrink cycle goes on.

      This post was spurred by a client situation where yet again, they were doing this exact thing – all because they’d read a blog saying it was important to rebuild their indexes to fix fragmentation. Meanwhile, they were blowing their maintenance windows every night, doing this mindless shuffling into the morning hours, slowing down performance for their end users. The fix? Just…stop doing that. Presto, short maintenance windows and happier users instantly.

      Reply
  • If you transfer your indexes into a new file, completely emptying out the original file, rebuild the indexes on the new file to pack things in nice and tight, shrink the original file and transfer the indexes back, then remove that now unused new file, you can accomplish both shrinking and reorganizing your data at the same time. It’s a huge pain and can take a while, but if you have a persistent size problem and limited space (we do on our testing space) it can help. Ultimately, better hardware is warranted, but we had a case where a database had a bunch of stuff removed from it, leaving a huge file that was significantly empty, and of course shrinking made things slower since it messed up the indexing.

    Reply
  • Nick Fotopoulos
    August 13, 2018 3:40 pm

    What about if you reindex first, then shrink the files after? xD

    Reply
  • […] What’s So Bad About Shrinking Databases with DBCC SHRINKDATABASE? […]

    Reply
  • Thanks for this post. I think I might add the URL to my email signature.

    If you can’t avoid shrinking then DBCC SHRINKFILE with the TRUNCATEONLY option might be a good route. You want to keep all your files near the same size, too. Rebuilding the Indexes will help spread the wealth, too. Don’t forget about HEAPS. Clean that fragmentation up by either building cluster indexes or ALTER TABLE..REBUILD.

    Reference: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-2017
    “Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent. target_size is ignored if specified with TRUNCATEONLY.”

    Reply
  • So how does this apply to SQL Azure? We are pushing close to our paid for limit (250GB), but when we look at the space used by the data we’re only at 40GB…. This suggests that we should shrink otherwise we will need to pay more. Our SQL Server space is creeping up slowly, and then intermittently drops (I assume Azure is running some maintenance job) but that’s not happened for a while so we’re looking at forcing the issue.

    Reply
  • I have a situation where we took a 1.1TB table with no clustered index, wrote it over to a new identical table, but has a PK, getting rid of unnecessary data that had been bloating the table, and was no longer being added. When it was all done, the new table is 1GB. I have deleted the old table. I now have 3.8TB available in a 4.1TB database. We have been doing a lot of cleanup of data, and don’t expect this to grow like that again any time soon. The used space is 288GB. I’ve only deleted the old files on a test copy of the DB. What is my best option to clear up this space if not using shrinkdb? I’m even fine with leaving 100% extra space to deal with the index rebuilds if that’s what is necessary, it will still be saving us 3.5TB of space. At this point, we’re more concerned with the time, as the shrinkdb hasn’t been finishing after days of running.

    Reply
    • Aimee – the better option would have been creating a new filegroup during the move, but too late for that now – you’ll have to resort to shrinking. Next time you’re dealing with multi-terabyte objects, though, you can plan a little better for that ahead of time.

      Reply
      • I hope I don’t have to do this again any time soon, but the pain of shrinking this database will probably have me looking for alternatives next time. Thanks for confirming my thoughts after reading this and some other articles on shrinkfile.

        Reply

Leave a Reply

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

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}