Kill a Running Shrink: Is It Safe to Stop DBCC SHRINKFILE?

You can kill a running shrink with the KILL command, and the database stays safe. The work the shrink already finished is kept, so nothing needs a rollback. The damage, if any, comes from the shrink itself.

The Question Behind This Post

A client had a huge database and poor performance. After the tuning work, the complaints came back. The DBA found a DBCC SHRINKFILE on the data file. It had been running for hours, and nobody knew who had started it. The DBA wanted to kill it and asked whether that was safe. Could it cause corruption, a long rollback or an unresponsive server?

Can you kill a running shrink without harm? Yes, it is safe. The test below shows what happens, on a database you can throw away.

Build a File With Free Space at the Front

A shrink has real work to do when the free space sits in front of the data. The demo creates a database named ShrinkKillDemo with two tables, then drops the first one. That leaves 1,160 MB of file with about half of it free. The data of the second table sits behind the hole, so the shrink must move it forward. The script waits until the dropped table has given its space back. The last query measures the fragmentation of the second table before any shrink.

IF DB_ID(N'ShrinkKillDemo') IS NULL CREATE DATABASE ShrinkKillDemo;
GO
ALTER DATABASE ShrinkKillDemo SET RECOVERY SIMPLE;
GO
USE ShrinkKillDemo;
GO
SET NOCOUNT ON;
DROP TABLE IF EXISTS dbo.OldOrders, dbo.NewOrders;
CREATE TABLE dbo.OldOrders (OrderID int NOT NULL CONSTRAINT PK_OldOrders PRIMARY KEY, Padding char(1000) NOT NULL DEFAULT 'x');
CREATE TABLE dbo.NewOrders (OrderID int NOT NULL CONSTRAINT PK_NewOrders PRIMARY KEY, Padding char(1000) NOT NULL DEFAULT 'x');
INSERT INTO dbo.OldOrders (OrderID) SELECT TOP (500000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
INSERT INTO dbo.NewOrders (OrderID) SELECT TOP (500000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
DROP TABLE dbo.OldOrders;
DECLARE @tries int = 0;
WHILE FILEPROPERTY(N'ShrinkKillDemo', 'SpaceUsed') / 128 > 600 AND @tries < 30
BEGIN
    WAITFOR DELAY '00:00:01';
    SET @tries += 1;
END;
SELECT name AS FileName, size / 128 AS FileMB, FILEPROPERTY(name, 'SpaceUsed') / 128 AS UsedMB FROM sys.database_files WHERE type = 0;
SELECT CONVERT(decimal(5,1), avg_fragmentation_in_percent) AS FragmentationPercent, page_count AS Pages
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.NewOrders'), 1, NULL, 'LIMITED');

Start the Shrink and Find It

Run the shrink in the first window. It asks for a 600 MB file, which is a little above the used space.

DBCC SHRINKFILE (ShrinkKillDemo, 600);

While it runs, open a second window. A running shrink shows up in the request view with the command name DbccFilesCompact. It reports how far it is, so you can see whether killing it now would waste much. Reading the view needs the VIEW SERVER STATE permission, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later.

SELECT r.session_id, r.command, CONVERT(decimal(5,1), r.percent_complete) AS PercentComplete, r.status, r.total_elapsed_time AS ElapsedMs
FROM sys.dm_exec_requests AS r
WHERE r.command LIKE N'Dbcc%';

This is one reading from the test run, two seconds after the start. Your session number differs. The status switches between running and suspended while the shrink works. The query returns no rows once the shrink has finished. That is the case when you run the script on one connection.

Kill It and Check the Damage

Use the session number from the second window. The statement below ends that session. It is the number from the query above, so replace it before you run it.

-- Replace 146 with the session_id from the previous query.
KILL 146;

The first window reports that its session ended. The client decides the wording. The sqlcmd tool printed that it could not continue because the session was in the kill state. A reader reported that SSMS prints a severe error and says the results should be discarded. Both messages describe the dead session. Neither one says the database is damaged.

Now check the database. The data file kept its size. NewOrders still held all 500,000 rows. The next block counts the rows, runs the shrink again and checks the whole database. DBCC CHECKDB prints nothing when it finds no errors.

SELECT COUNT(*) AS NewOrderRows FROM dbo.NewOrders;

DECLARE @t0 datetime2 = SYSDATETIME();
DBCC SHRINKFILE (ShrinkKillDemo, 600) WITH NO_INFOMSGS;
SELECT DATEDIFF(MILLISECOND, @t0, SYSDATETIME()) AS ShrinkMs;

SELECT name AS FileName, size / 128 AS FileMB, FILEPROPERTY(name, 'SpaceUsed') / 128 AS UsedMB FROM sys.database_files WHERE type = 0;

DBCC CHECKDB (ShrinkKillDemo) WITH NO_INFOMSGS;

SELECT CONVERT(decimal(5,1), avg_fragmentation_in_percent) AS FragmentationPercent, page_count AS Pages
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.NewOrders'), 1, NULL, 'LIMITED');

The table below comes from the two window test. On one connection, the first shrink finishes by itself. The second one then takes only a few milliseconds.

The second shrink finished in 3,977 ms. An uninterrupted shrink of an identical database took 11,540 ms. The pages moved before the kill stayed where they were, so the second run had less left to do. Your times will differ with your disk.

The Shrink Is the Real Problem

Poor performance during or after a shrink does not come from the kill. It comes from the shrink. A shrink moves pages from the end of the file into the free space at the front. It does not care about their order. The last query of the block above measured it. Before the shrink, the clustered index of NewOrders had 0.0 percent fragmentation. After it, the same 71,429 pages had 93.6 percent.

A rebuild of the index puts them back in order and takes the free space with it. A rebuild needs free space in the file. It can grow the file again, so plan it with the shrink in mind.

If a job shrinks your files every night, look at that job first. A nightly shrink is a habit to question. For a database that shrinks itself, read Database File Size Shrinks by Itself? Check Auto Shrink.

Should You Let It Finish?

You could argue that you should let the shrink finish, because a half-shrunk file wastes the work. The work is not wasted. The pages stay where they were moved, and the next run continues from there. Stopping it costs nothing. Letting it run costs fragmentation and disk traffic while your users wait.

What to Remember

It is safe to kill a running shrink. Find the session through the request view, kill it, and run CHECKDB if you want proof. Then ask why the shrink ran at all. Rebuild the indexes of the tables that it moved.

When you finish the demo, remove the database.

USE master;
GO
IF DB_ID(N'ShrinkKillDemo') IS NOT NULL
BEGIN
    ALTER DATABASE ShrinkKillDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE ShrinkKillDemo;
END;

A shrink is not a transaction you must finish, it is work you can stop and resume.

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