Previously: index work freed up several terabytes and cut write overhead. But freeing space inside the database is one thing; giving it back — and cutting the bill — is another.
Size matters here for a simple reason: as the business grows, so does the data, and so does the cost — not only for the database, but for backups and long-term retention. Those bills climb month over month, and we expect the growth to be non-linear rather than flat. So once the sharpest performance problems were under control, controlling size became the next front.
Used vs. allocated space
After the optimizations, the used space dropped, but the allocated space did not follow automatically. Allocated space grows as needed but never shrinks back on its own after deletes — the engine keeps that formatted space around so future inserts stay fast. On production, used space is down from its 18 TB peak to about 11 TB today. In reality we've freed even more than that number suggests: the migration and refactoring that are still in progress temporarily occupy extra space, which inflates the current figure. Once that work completes, used space will fall further — and by a lot: the refactoring lets us drop not just the huge indexes on some tables but several large tables entirely, indexes and all, so the final size should end up far smaller than today's. Reducing used space already helps — it slows the growth of cost — but the full financial win only comes when you actually reduce allocated space. And what a shrink can reclaim is precisely that gap — the allocated-but-unused space.
Why the order mattered
Freeing this space first wasn't optional — it was what made the rest work cleanly. Before the refactoring, allocated space sat just above used space — a small buffer on top. As we kept writing more data, used space grew and slowly pushed allocated up with it: a slow, steady rise. A big online index rebuild, though, behaves very differently: it builds a fresh copy of the index alongside the old one, so it needs several terabytes of temporary room all at once, roughly the size of the index being rebuilt. With almost no free room inside the database, that would have forced allocated space to jump up by the size of the index — pushing the number up just when the whole point was to bring it down. By reducing used space first, we created that headroom inside the database, so the rebuild reused space we already had instead of spiking allocated upward. And rebuilding those indexes (as described in Part 3) before the shrink paid off a second time. A freshly rebuilt index is tightly packed, and a shrink has to move far fewer pages that way — so doing the rebuilds first, the largest indexes included, made the shrink itself faster and able to reclaim more.
A cloud database won't do this for you
You might expect a managed cloud database to reclaim unused space automatically — but it won't hand allocated space back on its own (and automatic shrink is discouraged anyway). Reclaiming it is a manual operation using DBCC SHRINKFILE. And it isn't a routine maintenance task: Microsoft is explicit that shrink shouldn't be a regular operation — it's a one-off in response to a major drop in used space, which is exactly our case here, after the index cleanup. There's also a genuinely Hyperscale-specific catch: shrink only became generally available for Hyperscale on 29 January 2025, so it's a relatively new capability there. On a database this size it's a slow, careful, iterative process that runs for weeks, and it lines up with Microsoft's own guidance — run it in off-hours or a maintenance window, use WAIT_AT_LOW_PRIORITY to limit blocking, and so on.
Our conservative approach
The "shrink script" I developed is really orchestration around DBCC SHRINKFILE. It starts with the cheap win — a TRUNCATEONLY pass, which instantly releases any allocated-but-unused space sitting at the end of a file, with no data movement. Only then does it move into the slower, data-moving shrink, done in small chunks rather than one big pass — because if a live workload grabs the just-freed space before the file is truncated, the shrink can't reclaim it, so smaller chunks make each reduction more likely to stick. Shrink does increase fragmentation, but we tolerate that: it's cleaned up afterwards by our regular index maintenance — automatic for the smaller indexes, manual for the few largest. Files are handled separately, and the process can pause and resume — including backing off on its own when the database is busy with real work, so the shrink never competes with the live workload. The goal is to bring allocated space close to used space while leaving a sensible buffer for growth — enough that the database doesn't just auto-grow straight back the moment the shrink finishes. It will grow into that buffer over time, of course; the point is not to shrink so tight that it springs right back. This orchestration is deliberately — almost obsessively — conservative; it has to be, to run safely against a heavily loaded production database, and it's based on a lot of the lessons I learned while testing on the non-production databases first.
Results so far
True to "stability first," these come from non-production instances first (both now complete), before production:
One of our non-production databases: used space from 9 TB to 2 TB, and allocated from 9 TB down to 2.2 TB — bringing allocated right down to what's actually used, with just a small buffer for growth.
Another instance: used from 6 TB to 3.2 TB; allocated from 6 TB to 3.5 TB.
Production: used space is already down from 18 TB to 11 TB, and the production shrink is now running — we expect it to reclaim a little under 7 TB (a bit less than that, since we deliberately keep a buffer), bringing allocated space back down close to what's actually in use.
A note on backup cost
Backup cost, roughly speaking, has two parts: recent (short-term) backups and long-term retention. As the database got smaller, the recent backups shrank with it almost immediately. The long-term retention copies, though, are full backups taken from the larger database — they'll come down only gradually, as older backups age out under the retention policy and get replaced by new ones taken from the smaller database. So part of the saving is already here, and part will arrive over time.
Longer term, there's a plan already taking shape to move long-term-retention backups off the Azure-managed service and onto infrastructure we run ourselves — what's often called cloud repatriation, a broad and genuinely interesting topic in its own right — which should reduce backup cost further. That part will be delivered by our Systems Engineers Team, and implementation is expected to start soon. Shrinking the database helps here too: the smaller the backups, the less there is to migrate and manage — and the cheaper it all is to keep.
The bigger picture
None of these levers works alone. Automatic index maintenance, removing unused indexes, the ongoing refactoring, and partitioning together should reduce the database's size substantially — as a rough order of magnitude, to somewhere around 4–5 TB, perhaps less (hard to pin down precisely yet) — and, just as importantly, make its growth controlled rather than accelerating. The refactoring will also move that external client I mentioned earlier in the series onto our API instead of a direct connection, which means we can eventually retire its read-only replica — cutting infrastructure and operational cost further, and lowering the total cost of ownership of the whole setup — the database and everything around it.
Next, the part that tends to spark the most curiosity: where AI actually helped — safely.
Originally published as a LinkedIn post series, "The Database Marathon."
Top comments (0)