Ever ran DBCC SHRINKFILE, watched it finish without a single error… and then discovered that your file is exactly the same size as before? You run it again. Same thing. And again. Nothing. Meanwhile you know there are gigabytes of empty space in there, taunting you. Welcome to one of the most maddening little corners of SQL Server.
This post is a spiritual successor to my earlier one, Troubleshooting Long-Running SHRINK Operations. That post was about shrinks that run slowly. This one is about shrinks that run quickly, report success, and accomplish absolutely nothing. Different symptom, same family of misery.
TL;DR: Sometimes the fix is to grow your file a little bit and then shrink it again. I’ve baked this behavior into my Shrink a Database File in Specified Increments script via the new @RegrowOnRetry parameter.
Disclaimer
The mandatory warning, same as always: as a rule of thumb, shrink operations are to be avoided in SQL Server whenever possible. Fragmentation, the I/O cost, the inevitable re-growth… it’s all bad, and plenty of smarter people than me have written about why. Go read them.
But, as I admitted last time, there are those rare-but-real situations where you genuinely need to reclaim disk space and a shrink is the only tool you’ve got. Like maybe you migrated a huge table out, or you enabled data compression on half your database, or you’re simply scraping for every last gigabyte on a full disk. If that’s you, read on.
The Problem
Here’s the scenario. You’ve got a data file that is, say, 128 GB on disk, but only 11 GB of it is actually used. That’s over a hundred gigabytes of empty space you’d love to hand back to the operating system.
So you run the obvious thing:
DBCC SHRINKFILE (N'MyDataFile', 20000);
It runs. It finishes. No errors. You get the usual tidy result back. You go check sys.database_files, fully expecting a nice smaller number…
…and the file is still 128 GB. Not one megabyte smaller.
“Fine,” you mutter, “must’ve been a fluke.” You run it again. Finishes in a few seconds this time. Still 128 GB. You try shrinking by just 2 MB to see if anything moves. Nope. Still stuck at the exact same size. You try running CHECKPOINT, or various DBCC commands to flush data into the disk. But alas, the shrink keeps cheerfully telling you it succeeded, while doing precisely nothing.
This is different from the slow shrinks I wrote about before, and it’s also different from getting a nice, honest error 5240 (the “file cannot be shrunk as it is either being shrunk by another process or is empty” one). At least an error gives you something to grab onto. Here you get nothing – no error, no warning, no progress. Just a file that has quietly decided it is done shrinking, thankyouverymuch.
Why Does This Happen?
To understand the fix, you need to know how shrink actually works under the hood. Paul Randal has documented the algorithm, and it’s brutally simple:
- Determine the target size, which can never be smaller than the actual used space.
- Find the highest allocated page in the file.
- Move it as far toward the beginning of the file as it can, into free space in the part of the file you’re keeping. (Microsoft’s docs describe it as relocating the used pages from the area being freed into any available unallocated pages in the area being kept.)
- Repeat until nothing is allocated above the target, then lop off the now-empty tail.
In its default, page-moving mode (i.e. without the TRUNCATEONLY option), shrink genuinely does relocate those high pages toward the front (that’s steps 2 and 3 above) so most of the time it compacts everything down and the file happily shrinks to roughly the space actually in use. The wall only goes up when shrink reaches a page it cannot relocate. And because a file can never be truncated past its highest still-allocated page, a single un-movable page parked near the end sets the floor for the whole file, never mind the hundred empty gigabytes sitting lower down. Once shrink has moved everything it’s able to move and that one stubborn page is still stuck up there, all that free space beneath it becomes irrelevant and the truncation point is pinned.
So, what makes a page un-relocatable? A whole rogues’ gallery:
- Pages SQL Server flatly refuses to move – internal work table pages (a tempdb classic), pages belonging to an object that was just dropped, or pages tied to a paused resumable index build.
- Heap pages that won’t fit anywhere else – if relocating a heap page would push a row past the 8,060-byte limit (think wide tables relying on row-overflow), the move can fail outright due to an internal timeout.
- Leftover LOB allocations from columns that were NULLed or deleted but whose space lingers as reserved-but-awkward-to-move (see the LOB section of the previous post).
- Allocation-map and other system pages that live at fixed physical positions in the file by design.
Sometimes SQL Server is courteous enough to tell you, printing a “Page X:Y could not be moved…” message. That’s the noisy cousin of error 5240. (If you get that message, turn the page ID into the owning object: feed the file and page numbers into sys.dm_db_page_info or DBCC PAGE. Now you know exactly which object to un-heap, rebuild, or move out of the way.)
But other times it stays silent. It quietly completes, reports success, relocates nothing past the wall, and leaves you glaring at an unchanged file size. Re-run the exact same command and you get the exact same nothing – a loop of polite, useless “success.”
Microsoft even has a (very dry) name for this in the DBCC SHRINKFILE troubleshooting docs: the “error-less shrink operation” that doesn’t change the file size. Their first suggestion is to make sure the file actually has reclaimable free space to begin with, because sometimes what looks empty is really reserved space that shrink can’t touch. Worth ruling that out first. But once you’ve confirmed there genuinely is free space down there and the file still won’t budge, congratulations: you’re in stuck-page territory.
The (Slightly Weird) Solution: Grow It, Then Shrink It
Here’s the trick that sounds completely backwards: before you try to shrink again, grow the file a little bit.
Yes, grow. Make the file bigger on purpose, by a small amount, and then run your shrink again.
I first ran into this at an actual customer environment. Their data file had gotten “stuck” at a certain size, shrink reported success, file wouldn’t budge, the whole song and dance described above. On a hunch, we bumped the file size up by a small increment with a plain ALTER DATABASE … MODIFY FILE, then ran the shrink again, and it suddenly worked, sailing right past the size it had been glued to for ages. Celebrations ensued. 🥳
And then, sometime later, it got stuck again at a new size. Same fix: grow a little, shrink again, and it broke through the new wall too. We ended up doing this dance several times to get the file all the way down.
So why on earth does growing it first help? Well, I’m not actually 100% sure but I have a theory: re-running the identical DBCC SHRINKFILE against an unchanged file hands the engine the identical allocation layout. So, it makes the identical decisions and dies in the identical spot. You’re not giving it anything new to chew on. In other words, you’re simply re-running the same deterministic algorithm and getting the same deterministic result.
Growing the file, however, changes the inputs of the algorithm. You’ve altered the file’s free-space map and shuffled what’s allocated where, so when shrink runs its relocation pass again, it makes different choices this time, and that’s often enough to knock loose the page that was pinning the high-water mark and let the file finally truncate below the old floor.
To be honest, I have not found Microsoft – or anyone else, for that matter – documenting precisely why a small regrow breaks the jam, and a page can be “un-movable” for an annoying variety of state- and timing-dependent reasons. So treat the above as a well-reasoned model, not gospel. I won’t dress this up with borrowed authority: this isn’t a trick I read in the docs or on someone’s blog. It came straight out of a real customer engagement, where a data file had been stuck at the same size for ages. We grew it a bit, shrank it again, and it broke loose. And every time it later hit a new wall, the same move broke it loose again. The result was consistent and repeatable enough that I trusted it enough to bake it into the script.
One important distinction to make: everything here is about regular user-database data files. Tempdb is its own special creature. It refuses to shrink for its own reasons (those internal worktables, mostly), and it has its own bag of fixes like DBCC FREEPROCCACHE, CHECKPOINT, DBCC DROPCLEANBUFFERS, or simply a service restart (since tempdb is recreated from scratch on startup anyway). If your stuck file is tempdb, go read up on those instead – and don’t point @RegrowOnRetry at it expecting magic.
Enter @RegrowOnRetry
Doing the grow-then-shrink dance by hand is tedious, and you can’t always sit there babysitting a shrink at 2 AM. So I’ve added this behavior straight into my Shrink a Database File in Specified Increments script as a new parameter:
,@RegrowOnRetry BIT = 1 -- Inability to shrink may be resolved by temporarily increasing the file size before shrinking it again.
Here’s how it fits into the script’s logic. The script already shrinks your file gradually, in increments of @IntervalMB (50 MB by default). After each increment, it re-checks the file’s actual size to confirm the shrink really happened. If the file didn’t get smaller (i.e. it’s stuck) the script retries, up to @IterationMaxRetries times (3 by default).
With @RegrowOnRetry = 1 (the new default), each of those retries now does the trick automatically: it grows the file by one increment, then loops back around and tries to shrink it again. If that breaks the jam – great, the script carries on shrinking and resets its retry counter. If the file is truly stuck even after the regrow attempts have been exhausted, the script gives up gracefully on that boundary instead of spinning forever.
If you’d rather not have the script ever grow your file, just set @RegrowOnRetry = 0 and it’ll behave the old way.
Two regrows, two different problems
It’s worth being clear about this, because the script now has two “grow to get unstuck” behaviors and they handle different situations:
@RegrowOnError5240(added back in 2020) reacts to an explicit error: error 5240, raised when a file can’t be shrunk because it’s being shrunk by another process or is empty. That’s a loud, obvious failure.@RegrowOnRetry(the new one) reacts to the silent failure: no error at all, the shrink “succeeds,” but the file size simply doesn’t go down. This is the frustrating case this whole post is about.
Both parameters default to on, so out of the box the script now has your back for both the noisy and the sneaky-quiet flavors of a stuck shrink.
How to Use It
Grab the latest version of the script from our GitHub repo, set your @DatabaseName, @FileName, and @TargetSizeMB (or @MaxPercentUsed) as needed, and run it.
A few tips if you’re new to the script:
- Start with
@WhatIf = 1to print the commands without executing anything, so you can see exactly what it intends to do. - Keep
@IntervalMBsmall (the default 50 MB is usually sensible). Smaller increments are both gentler on the server and give the regrow-and-retry logic finer control over how much it grows back. However, if you see that the shrink is progressing swiftly through the increments, consider increasing the increment to make it go even faster. - Mind the safety valves the script already provides for busy systems and Availability Groups:
@DelayBetweenShrinks,@MaxActiveTranLogSizeMB/@MaxActiveTranLogSizePct,@MaxReplicaRecoveryQueue, and@TimeLimit. A shrink that grows-then-shrinks repeatedly generates extra transaction log and extra work for your AG replicas, so don’t ignore these on production without thinking it through.
And of course, before you run any of this in production, make sure you have good, tested backups. Backups are your soul-mates. 💍
Conclusion
A shrink that reports success while doing nothing is one of those problems that makes you question your sanity. The good news is that it’s usually not a corruption or a bug on your end. It’s just SQL Server stubbornly pinned to a single page at the end of your file, refusing to relocate it. And the cure, counter-intuitive as it is, is often to give the file a little room to breathe: Grow it slightly, then shrink it again.
That’s exactly what @RegrowOnRetry now does for you, automatically, on every retry.
As always, the real question isn’t how to shrink – it’s whether you should be shrinking at all. Are you sure? Are you really sure? 🤔 Really, really, cross your heart and hope to get your disk space back? 😬 If the answer is still yes, then I hope this gives you one more tool to get the job done with a little less hair-pulling.
Good luck! 👍
See Also
- Troubleshooting Long-Running SHRINK Operations – the original post, for when your shrink is slow rather than stuck.
- How to Un-Heap your Heaps (Resolving Tables Without Clustered Indexes)
- Shrink a Database File in Specified Increments – the script itself.
- DBCC SHRINKFILE (Transact-SQL) – the official documentation.
- When Shrinking Tempdb Just Won’t Shrink – specific use case for stubborn Tempdb files