A Shrink That Stops Blocking Others: SHRINKFILE WAIT_AT_LOW_PRIORITY

SHRINKFILE WAIT_AT_LOW_PRIORITY lets a shrink step aside when it would otherwise get in the way of your users. The shrink queues politely behind other sessions, and you choose what happens if the wait runs long. Shrinking is still a rare tool, not a habit.

The Friday cleanup that blocks the app

Here is a familiar story. You archived a big table last week, and the data file is now mostly empty. Someone says, “Let’s shrink it and get the disk back.” You run DBCC SHRINKFILE at 4 PM on Friday.

The shrink needs locks. Sessions that were fine a minute ago start to queue behind it. Your phone rings. A normal shrink does not care that it is in the way.

The WAIT_AT_LOW_PRIORITY option fixes that politeness problem. It does not fix the other problem, which is that shrinking is often a bad idea. We will look at both.

Build a database worth shrinking

The demo creates a database called SqlAuthorityDemo and drops it at the end. The data file starts at 64 MB. Two tables are filled together, one row each in turn, so their pages are mixed through the file. One row fills about one page.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
CREATE DATABASE SqlAuthorityDemo;
GO
ALTER DATABASE SqlAuthorityDemo SET RECOVERY SIMPLE;
ALTER DATABASE SqlAuthorityDemo MODIFY FILE (NAME = SqlAuthorityDemo, SIZE = 64MB);

USE SqlAuthorityDemo;
SET NOCOUNT ON;

CREATE TABLE dbo.KeepRows (Id int IDENTITY PRIMARY KEY, Pad char(8000) NOT NULL DEFAULT 'k');
CREATE TABLE dbo.JunkRows (Id int IDENTITY PRIMARY KEY, Pad char(8000) NOT NULL DEFAULT 'j');

DECLARE @i int = 0;
WHILE @i < 1500
BEGIN
    INSERT dbo.KeepRows DEFAULT VALUES;
    INSERT dbo.JunkRows DEFAULT VALUES;
    SET @i += 1;
END;

Look before you shrink

Check the file size, the used space, and the fragmentation of the table you plan to keep. Then drop the junk table, which leaves holes between the pages of the table you kept. In my run the file is 64 MB and, with both tables in place, about 27 MB is used. Fragmentation of the kept table is under one percent.

SELECT name,
       CONVERT(decimal(10,1), size / 128.0) AS SizeMiB,
       CONVERT(decimal(10,1), FILEPROPERTY(name, 'SpaceUsed') / 128.0) AS UsedMiB
FROM sys.database_files
WHERE type = 0;

SELECT avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.KeepRows'), 1, NULL, 'LIMITED');

DROP TABLE dbo.JunkRows;

Shrink with the polite option

Now the shrink itself. The target is 20 MB. The WAIT_AT_LOW_PRIORITY clause tells the shrink to queue behind other sessions instead of ahead of them. ABORT_AFTER_WAIT says what to do when the wait ends. NONE keeps waiting as a normal request. SELF gives up the shrink. BLOCKERS ends the sessions in the way.

I use SELF every time. BLOCKERS means a user loses their work so a file can get smaller. That is a poor trade.

DBCC SHRINKFILE (N'SqlAuthorityDemo', 20)
    WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);
GO
SELECT name,
       CONVERT(decimal(10,1), size / 128.0) AS SizeMiB,
       CONVERT(decimal(10,1), FILEPROPERTY(name, 'SpaceUsed') / 128.0) AS UsedMiB
FROM sys.database_files
WHERE type = 0;

SELECT avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.KeepRows'), 1, NULL, 'LIMITED');

The file is now 20 MB, my target. But look at the second result. Fragmentation of the kept table jumped from under one percent to about 30 percent in my run. The shrink moved pages from the end of the file into the holes at the front, and their order got scrambled. Your exact number will differ.

I did not create a second session to hold a lock here. So this demo shows that the command works. It does not show the waiting behavior itself. Test that on a quiet test server with two query windows before you trust it.

Repairing the damage makes the file grow back

The usual fix for fragmentation is a rebuild. A rebuild needs working room to build the new copy. The file has only a few free megabytes now, so it grows again. The result shows fragmentation of zero, and in my run the file ended at 84 MB, bigger than the 64 MB I started with. Your growth step depends on your autogrowth setting.

ALTER INDEX ALL ON dbo.KeepRows REBUILD;

SELECT name,
       CONVERT(decimal(10,1), size / 128.0) AS SizeMiB,
       CONVERT(decimal(10,1), FILEPROPERTY(name, 'SpaceUsed') / 128.0) AS UsedMiB
FROM sys.database_files
WHERE type = 0;

SELECT avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.KeepRows'), 1, NULL, 'LIMITED');
GO
USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

That is the whole point. You shrank the file, fragmented the table, rebuilt it, and the file grew back. The disk space you wanted never really came back.

When a shrink is actually worth it

Shrink after a one-time drop in data that will not return, such as a purge or a retired archive. Make sure the files will not need that space again. Do not put it in a nightly job. And if you shrink, use SELF so it steps aside for your users.

Next time someone asks for a shrink, ask first whether the space will be needed again.

A polite shrink is not a good shrink, it is a rare cleanup done with manners.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.