Shrinking a SQL Server data file safely: DBCC SHRINKFILE in steps, low-priority waits and fixing the fragmentation

#When shrinking is the right call

Shrinking a data file is usually a bad habit, but there are legitimate one-off reasons: you archived or purged a large share of a database, dropped a big table, moved data to another filegroup, or need to retire an extra .ndf file. If the space will be reused within weeks, leave it alone. The file will just grow again, and every grow and shrink cycle costs I/O and fragmentation.

What shrink actually does: it moves pages from the end of the file into free space near the start, then releases the empty tail to the operating system. Moving pages breaks the logical order of indexes, so a shrink almost always leaves you with fragmented indexes that need attention afterwards.

Warning

Shrinking a large file generates a lot of transaction log, takes locks on the objects it moves and can run for hours. Run it in a maintenance window or in small steps, and watch the log and any Availability Group send queue while it runs.

#Step 1: check how much space is really free

Do not trust the file size on disk. Ask SQL Server how much of each file is used:

USE YourDatabase;
SELECT  name,
        type_desc,
        physical_name,
        size / 128.0                                   AS size_mb,
        FILEPROPERTY(name, 'SpaceUsed') / 128.0        AS used_mb,
        (size - FILEPROPERTY(name, 'SpaceUsed')) / 128.0 AS free_mb
FROM    sys.database_files;

Decide on a target that keeps headroom for normal growth, typically used space plus 10–20%, or a few months of growth. Shrinking to zero free space guarantees an autogrow during the next busy period.

Also confirm nobody turned on auto-shrink, which repeats this damage on a timer:

SELECT name, is_auto_shrink_on FROM sys.databases WHERE is_auto_shrink_on = 1;
ALTER DATABASE YourDatabase SET AUTO_SHRINK OFF;

#Step 2: try TRUNCATEONLY first

If the free space is already at the end of the file, you can release it without moving a single page:

DBCC SHRINKFILE (N'YourDatabase_Data', TRUNCATEONLY);

This is fast and causes no fragmentation. Re-run the Step 1 query. If the file barely changed, the free space is scattered through the file and you need a real shrink.

#Step 3: shrink in small steps

One big shrink holds locks and log space for the whole run. Small steps let log backups clear the log between iterations and let you stop cleanly. The work already done is kept if you cancel a running DBCC SHRINKFILE.

On SQL Server 2022 and later, Azure SQL Database and Azure SQL Managed Instance, add WAIT_AT_LOW_PRIORITY. The shrink then waits for its schema modification lock at low priority instead of queuing in front of user queries. With ABORT_AFTER_WAIT = SELF, it gives up after the wait (one minute) and does nothing, rather than causing a blocking chain.

-- Shrink a data file in 1 GB steps down to a target size (MB).
DECLARE @file    sysname = N'YourDatabase_Data',
        @target  int     = 200000,   -- final size in MB
        @step    int     = 1024,     -- MB per iteration
        @current int;

SELECT @current = size / 128 FROM sys.database_files WHERE name = @file;

WHILE @current > @target
BEGIN
    SET @current = CASE WHEN @current - @step < @target THEN @target ELSE @current - @step END;
    RAISERROR('Shrinking %s to %d MB', 0, 1, @file, @current) WITH NOWAIT;

    DBCC SHRINKFILE (@file, @current) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);
    -- On SQL Server 2016-2019 remove the WITH clause.

    SELECT @current = size / 128 FROM sys.database_files WHERE name = @file;
END;

WAIT_AT_LOW_PRIORITY only applies to data files, not the transaction log. ABORT_AFTER_WAIT = BLOCKERS also exists, but it kills the user transactions blocking the shrink. Use it only if you have agreed that with the application owners.

Caution

In a database in the FULL recovery model, the page moves are logged. Keep your normal log backups running during the shrink, or the log file will grow and you will have swapped one disk problem for another. In an Availability Group, the same log has to reach every secondary.

#Watch progress

From another session:

SELECT  r.session_id,
        r.command,
        r.status,
        r.percent_complete,
        r.wait_type,
        r.blocking_session_id,
        r.total_elapsed_time / 60000 AS elapsed_min
FROM    sys.dm_exec_requests AS r
WHERE   r.command LIKE 'Dbcc%';

percent_complete is filled in for shrink operations. The exact command text varies by version, so the LIKE filter catches it. A non-zero blocking_session_id means the shrink is waiting on a user transaction, or the other way round.

#Why a shrink stalls or crawls

#Step 4: fix the fragmentation you just created

Check index fragmentation on the larger indexes after the shrink:

SELECT  OBJECT_SCHEMA_NAME(ips.object_id) + '.' + OBJECT_NAME(ips.object_id) AS table_name,
        i.name  AS index_name,
        ips.avg_fragmentation_in_percent,
        ips.page_count
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
JOIN    sys.indexes AS i
        ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE   ips.page_count > 1000
  AND   ips.avg_fragmentation_in_percent > 30
ORDER BY ips.page_count DESC;

Choose the repair carefully:

• ALTER INDEX ... REORGANIZE works in place and needs very little free space. It fits well after a shrink.
• ALTER INDEX ... REBUILD builds a new copy of the index first, so it needs free space roughly the size of the index. Rebuilding big indexes will grow the file you just shrank. This is why "shrink, then rebuild everything" ends where it started.

ALTER INDEX IX_Orders_CustomerId ON dbo.Orders REORGANIZE;
UPDATE STATISTICS dbo.Orders IX_Orders_CustomerId;

If you rebuild, leave enough free space in the target size for the largest index you plan to rebuild.

#Removing an extra data file completely

To retire a secondary data file, move its pages to the other files in the same filegroup, then drop it:

DBCC SHRINKFILE (N'YourDatabase_Data2', EMPTYFILE);
ALTER DATABASE YourDatabase REMOVE FILE YourDatabase_Data2;

EMPTYFILE also stops new data going into that file. The other files in the filegroup need enough free space or autogrowth to absorb the data. You cannot remove the primary data file.

#Azure SQL Database and Managed Instance notes

• In Azure SQL Database, storage is billed and limited on allocated space, not used space. A large purge does not lower allocated space until you shrink. Use the same sys.database_files query and DBCC SHRINKFILE steps. Azure SQL Database also supports WAIT_AT_LOW_PRIORITY.
• Shrinking can affect performance while it runs on busy General Purpose or Business Critical databases. Schedule it off-peak.
• On Amazon RDS for SQL Server, DBCC SHRINKFILE works as the master user. The storage you provisioned for the instance does not shrink, though. Only the database files do.

#Prevent it next time

• Size data files for expected growth and set fixed-MB autogrowth, not percentage growth.
• Keep AUTO_SHRINK off everywhere. Add a check to your estate inventory.
• Purge with a planned retention job in small batches, and decide before the purge whether the space will be reused or reclaimed.
• For very large archives, move historical data to a separate filegroup. Later you can empty and remove that file, or drop a partition, instead of shrinking the main file.
• After any shrink, schedule a REORGANIZE and statistics update for the affected indexes.