Shrinking a Data File in Chunks Without Locking Everything Up

I want to say upfront: shrinking a data file is not something I do as routine maintenance, and I wouldn't recommend it as one either. It fragments indexes, it can generate a lot of I/O and transaction log activity while it runs, and on an active production database it can make things feel slower right when you least want that. Most of the time, the correct answer to "the data file is huge" is "add more storage" or "find out why it grew and fix that instead."

That said, I've been in the situation more than once where none of that is possible in the moment

the disk is genuinely almost full, there's no budget or approval to expand storage today, and I need to claw back some space right now without taking the database offline. This post is about the approach I use in that specific scenario: shrinking gradually, in small steps, rather than firing one large DBCC SHRINKFILE command and hoping for the best.

Why not just run DBCC SHRINKFILE once with the target size?

A single shrink to a much smaller target size tends to be a long-running, all-or-nothing operation. It holds onto resources for the entire duration, it's harder to interrupt safely partway through, and if something goes wrong (disk fills up further, a blocking issue appears, the business needs the maintenance window back) you're stuck either killing a long transaction-like operation or riding it out. Shrinking in small percentage steps, with a short pause between each step, gives me:

A natural checkpoint between steps - I can stop after any iteration if I need the environment back, without having wasted the whole effort.
Visibility into progress rather than one long "please wait" black box.
A gentler pace of internal data movement, since each step is moving comparatively little data around instead of one enormous reorganization in a single shot.

The script

This is the version I actually use. It works out the current file size, then repeatedly shrinks it down by a fixed percentage each loop until it approaches zero (in practice you'd stop it manually once you've freed enough space - see the note below).

-- Always confirm the logical file name first
SELECT * FROM sys.database_files;

DECLARE @FileName sysname = N'dbname';
DECLARE @TargetSize INT = (SELECT 1 + size * 8. / 1024 FROM sys.database_files WHERE name = 'dbname');
DECLARE @Factor FLOAT = .845;

WHILE @TargetSize > 0
BEGIN
    SET @TargetSize *= @Factor;
    DBCC SHRINKFILE(@FileName, @TargetSize);
    DECLARE @msg VARCHAR(200) = CONCAT('Shrink file completed. Target Size: ',
         @TargetSize, ' MB. Timestamp: ', CURRENT_TIMESTAMP);
    RAISERROR(@msg, 1, 1) WITH NOWAIT;
    WAITFOR DELAY '00:00:10';
END;

A few notes on what's actually happening here:

sys.database_files gives me the real logical file name and current size in 8 KB pages, so I'm not guessing or hardcoding a size that's already stale.
The @Factor of .845 is arbitrary - it just controls how big a bite each iteration takes out of the file. A factor closer to 1 (like .95) shrinks more conservatively per step; something smaller shrinks more aggressively per step. I tend to start conservative on a production system.
The WAITFOR DELAY '00:00:10' between iterations gives the instance a short breather and gives me a window to watch what's happening (blocking, log growth, disk I/O) before the next chunk runs.
In practice, I don't let this loop run unattended to completion. I watch it, and once I've freed the amount of space I actually needed, I stop it (or just don't let the next iteration continue). The WHILE @TargetSize > 0 condition is there so the loop has a natural end state rather than running forever, not because I actually want to shrink the file down to nothing.

Watching progress while it runs

Shrink operations show up as a wait type you can query directly, which is useful for confirming it's actually making progress rather than stuck:

SELECT
    r.session_id,
    r.command,
    r.percent_complete,
    r.estimated_completion_time,
    r.wait_type
FROM sys.dm_exec_requests AS r
WHERE r.command LIKE 'DBCC%';

percent_complete gives a rough sense of how far along the current DBCC SHRINKFILE call is, which is helpful context when deciding whether to let the current iteration finish or reassess.

The part that's easy to forget: cleanup afterward

Shrinking moves pages around to free up space at the end of the file, and that process fragments indexes - often badly. I treat this as a required follow-up step, not an optional one. Once I've freed the space I needed, I go back and rebuild or reorganize the affected indexes:

-- Check fragmentation afterward
SELECT
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name AS index_name,
    ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
JOIN sys.indexes AS i
    ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
ORDER BY ips.avg_fragmentation_in_percent DESC;

From there it's the usual decision: ALTER INDEX ... REORGANIZE for lighter fragmentation, or ALTER INDEX ... REBUILD for anything more severe, keeping in mind that a rebuild will itself use additional space temporarily and generate its own transaction log activity - which is a little ironic right after you've just fought to free up disk space, but it's the trade-off for leaving the database with healthy indexes instead of a smaller file and degraded query performance.

Where this fits in the bigger picture

That last box matters as much as the technical steps. Freeing space is a stopgap - it buys time. It doesn't address why the file grew that large in the first place (unchecked table growth, missing archiving, a runaway process, log file issues masquerading as data file issues, and so on). I always follow up with that investigation once the immediate pressure is off, so I'm not back in the same spot again in a few weeks.

More posts on SQL Server performance, AWS architecture, and AI-assisted DBA automation are on the way.