We Cut a 190 GB Production Database to 45 GB in One Night. Here's What We Learned.

Our flagship internal application had a storage problem. Twelve years of accumulated project data had pushed the production database past 190 GB, and we were about to migrate it to a managed cloud instance where every gigabyte carries a monthly bill.

The mandate was simple: get it under control before the migration. The execution was less simple.

Here's how it went, and the six things I'd tell anyone attempting the same.

---

Lesson 1: Your database is probably smaller than you think

The first number everyone quoted was 190 GB. That's what the file size said, and that's what went into the planning deck.

Then we actually measured it:

SELECT name,
       CAST(size*8/1024.0/1024 AS decimal(10,1)) AS AllocatedGB,
       CAST(FILEPROPERTY(name,'SpaceUsed')*8/1024.0/1024 AS decimal(10,1)) AS UsedGB
FROM sys.database_files;

Allocated: 190.6 GB. Actually used: 106.7 GB.

Eighty-four gigabytes of the file was empty space — pages that had been allocated during past growth spikes and never released. The database had been quietly lying to us for years.

This changed the entire plan. We didn't need to delete 150 GB of data. We needed to delete about 65 GB and then reclaim the empty space that was already there.

Takeaway: allocated size and used size are different numbers, and stakeholders almost always quote the wrong one. Measure both before you scope anything.

---

Lesson 2: Size doesn't correlate with row count

We built a per-table inventory expecting the biggest tables to be the ones with the most rows. Mostly true — but the outliers were where the real wins were.

One configuration table held 13 GB across 21,000 rows. That's roughly 640 KB per row. It was storing serialized blobs, and nobody had ever cleaned it up because it "only had twenty thousand rows in it."

An attachment metadata table: 3.7 GB across 8,700 rows.

Meanwhile a history table with 30 million rows took 2.2 GB, because it was six narrow integer columns.

Takeaway: rank tables by megabytes, not row count. The blob-heavy tables hiding behind small row counts are often the fastest wins available — and they're invisible if you only look at sys.partitions.rows.

(Related trap: if you join sys.partitions to sys.allocation_units to get sizes, your row counts get multiplied by 2–3× for any table with LOB or overflow allocation units. Trust the MB column from that query; get row counts from a separate `COUNT()`.)*

---

Lesson 3: The transaction log will kill you before the data file does

This is the one that nearly ended the night early.

We had 40 GB free on the data volume. The plan was to delete ~65 GB of rows. In full recovery mode, with no log backups running (we'd stopped the scheduler for the maintenance window), every one of those deletes would accumulate in the transaction log until the volume filled and the whole operation rolled back — a rollback that would itself need log space it didn't have.

Delete operations log the full row image plus every index entry removed. Log volume typically meets or exceeds the data volume being deleted. We were looking at 65+ GB of log growth into 40 GB of free space.

The fix was one line:

ALTER DATABASE MyDatabase SET RECOVERY SIMPLE;

In simple recovery, the log truncates at every checkpoint. Ours stayed at 6.5 GB for the entire three-hour run — it never grew a single megabyte.

This is safe specifically because of the conditions we'd established: applications were offline, no users were writing, and we had a verified full backup as our rollback. We weren't giving up anything we needed.

Critical follow-up: switching back to full recovery afterward is not enough on its own.

ALTER DATABASE MyDatabase SET RECOVERY FULL;
BACKUP DATABASE MyDatabase TO DISK = '...' WITH COMPRESSION, INIT;

Until that backup runs, the database sits in what's often called "pseudo-simple" mode — the recovery chain is broken and log backups will fail. This step gets skipped constantly. Don't skip it.

---

Lesson 4: Gates and abort flags, not rollbacks

The purge script was structured in six waves, each handling a dependency layer — child records first, then parents, then orphan sweeps.

Between every wave sat a gate: a verification query that confirmed zero remnants before the next wave was allowed to start. And before every destructive batch, the script checked a control table:

IF EXISTS (SELECT 1 FROM dbo.Purge_RunState WHERE Aborted = 1)
BEGIN
    PRINT 'SKIPPED (aborted)';
    RETURN;
END

That abort flag meant a second session could halt the run at the next batch boundary without triggering a multi-hour rollback of work already committed. During UAT it saved us more than once.

The gates also caught something we would otherwise have missed. Wave A was planned to delete 6,435,445 rows. It deleted 8,824,935 — a difference of exactly 2,389,490. Those were orphaned parent headers whose children had just been removed, and the script logged them explicitly. Because the number reconciled to the row, we knew it was correct behaviour rather than a bug.

Takeaway: design for stopping, not for undoing. On a dataset this size, rollback is a worse outcome than a controlled halt.

---

Lesson 5: Prove what you didn't delete

The single most valuable thing in the script wasn't a delete — it was a set of before/after assertions on the data we promised not to touch.

Active projects   before=2154     after=2154        (must be equal)
Timesheet records before=4391749  after=4391749     (must be equal)
Task records      before=3536910  after=3536910     (expected drop=0)

Every stakeholder conversation about a purge eventually arrives at the same question: "How do you know you didn't delete something important?"

"We were careful" is not an answer. "Here are the exact counts of your financially-relevant records before and after, and they are identical" ends the conversation.

Take a snapshot of the protected data before you start. Verify against it after. Put both numbers in the communication.

---

Lesson 6: Deleting rows doesn't shrink the file

We finished the purge at 43 GB of actual data. The file was still 190.6 GB.

Deleting rows frees space inside the file; it doesn't return it to the operating system. That requires an explicit shrink — and shrinking 190 GB down to 45 GB means physically relocating well over 100 GB of pages.

Don't do that in one statement. A single massive SHRINKFILE can run for many hours, and cancelling it mid-flight loses everything it accomplished. Step it down instead:

DBCC SHRINKFILE (MyDatabase, 150000);
DBCC SHRINKFILE (MyDatabase, 110000);
DBCC SHRINKFILE (MyDatabase,  85000);
DBCC SHRINKFILE (MyDatabase,  65000);
DBCC SHRINKFILE (MyDatabase,  46000);

Each step completes in a manageable window, and if you have to stop, you keep the ground you've gained.

Be aware of the cost: shrinking works by moving pages from the end of the file to the front, which destroys index ordering. Expect heavy fragmentation afterward and plan an index rebuild — which will grow the file back somewhat. Budget for that before you report a final number.

---

The result

Metric	Before	After
File size	190.6 GB	44.9 GB
Actual data	106.7 GB	43.0 GB
Transaction log	6.5 GB	6.5 GB (never grew)
Runtime	—	3h 02m
Failed gates	—	0
Active records lost	—	0

Roughly 146 GB returned to the operating system, and a database that will cost meaningfully less to run in the cloud.

---

What I'd do differently

Measure earlier. Two weeks of planning were built on a number that turned out to be 44% empty space. One query on day one would have reframed the entire project.

Inventory everything, not just the suspects. Our purge script targeted 22 tables. A full-database inventory run late in the process surfaced two more totalling 5.7 GB that nobody had thought about — one of them the parent of a table we were purging, which meant we were about to create orphans.

Decide the retention policy before the maintenance window. We finished the night 3 GB above our stretch target, blocked entirely on one table that needed a business decision about how much history to keep. That decision could have been made a week earlier over email. Instead it became a follow-up item, and follow-up items have a way of becoming next quarter's items.

Take the backup twice. Once before you start. Once after you finish. The second one is the one people skip because they're tired and it's 4 AM, and it's the one that matters if something surfaces on Monday.

---

Every environment is different. Test on staging first — every wave and every retention rule in this operation was validated against a full production restore before it touched anything real. That discipline is the reason there's a blog post about a successful night instead of a post-mortem about a bad one.