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

41 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
    • This is what I wondered: The whole problem appears to be around the part where you defrag the index, and leave behind the blank space after the new, sorted index is in place. Why can’t we just use a different FILEGROUP for the indexes and this whole debate becomes moot?

      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
  • So imagine there is a database file 2Tb in size, 90% free space and want to set up a replica in a remote datacenter.
    Does it make sense to do the shrinking BEFORE setting up a replication to reduce initial replication time from few hours to tens of minutes?

    Reply
    • Johan – the answer would depend on:

      • Whether the space was put in on purpose (like the database is en route to growing to 2TB)
      • How the replication is being done (for example, with database backups, empty space won’t significantly impact your backup size)
      • Whether the commenter was trying to troll me for points

      Hope that helps!

      Reply
      • Pre-allocating 2TB of empty file space on a disk for a DB that is “Someday going to grow” is not the best method. “Johan” has a point: Large files take time to process. Backup systems, VM Storage migrations, et cetera, from a systems perspective, all work best when files are small.

        I sincerely hope that DBA’s out there are aware of the use of storage from a systems perspective, and always try to keep their db’s as small as possible.

        Reply
  • Hey Brent,
    Would dropping and recreating the index bypass the need for the data file to grow (as it does for rebuild index)? And if so, what is the downside to using drop-create index instead of rebuild index?

    Reply
    • Aoabishop – if you have Standard Edition, creating an index takes the table offline. Even if you have Enterprise Edition, you can create the index (mostly) online, but while it’s happening, queries are doing table scans because there’s no old version of the index to use.

      Reply
  • We need a way to be able to delete old SQL data and reduce the size of the overall storage. This is called reality and is needed over and over again. Here’s a scenario….you need to delete old SQL data that’s been unused for 10 years. Has Microsoft not figured out a way to do this with SQL?

    Could care less about whatever mechanism is used, but please tell DBA’s that storage is finite. Since people are afraid to do cleanups we’re stuck with gobs of old data that have to be backed up endlessly.

    Cleaning up data is good and reducing storage space is good as well.

    Reply
    • Mike – allow me to rephrase: “developers need to keep up with deletes regularly rather than only doing it once every 10 years. Have developers not figured out a way to do this with T-SQL?”

      When you phrase it that way, your request is kinda on shaky ground. 😉

      Reply
      • Agree with Brent that it is the developer’s job as the DBAs would not always know the business logic required to purge or archive data. This is a missed requirement on so many applications. As a DBA I can let you know storage space is finite. I doubt any DBA would argue that point. Finite seems to occur between 10 PM – 4 AM.

        Large databases have a negative downstream impact, too. Size of backups, space needed to restore to lower environments.

        Reply
  • Brent,
    As you know there are good reasons (e.g. old & outdated data) to nuke old data and shrink files. It just seems crazy that after all the great SQL improvements over the years, DBA’s can’t safely nuke old data and shrink the files.

    Reply
    • MikeG – as you know, people who say “as you know” come off as condescending buttholes. May wanna step away from the keyboard for a while, get some fresh air.

      Reply
  • Wow. Given your comment and the number of methods suggested, I’d gather that no, Microsoft still hasn’t found an easy way to delete data and shrink data files.

    Reply
  • What if you put all your indexes in a separate FILEGROUP? Then all these concerns about the whitespace left over after an index is defragged are nullified.

    Reply
  • Have a look at partioning for removing old data perhaps. At the end of the day…. you need space somewhere at some time and frequent shrkinking is like beating your head against a wall.

    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":""}