A data file shrink can run for hours because SQL Server must move allocated pages away from the file's end before it can return space to Windows. DBCC SHRINKFILE is especially painful when pages belong to heaps or large object data. Check whether space really needs to leave the file, then monitor and work in bounded steps.
Decide Whether Shrink Is Necessary
Free space inside a data file is reusable by the database. Returning it to the operating system makes sense after a one-time large purge or a permanent workload reduction, not as a nightly habit. Repeated shrink and regrowth create I/O, log activity, and fragmentation while giving back space the database soon needs again.
I start with current file size, used space, expected growth, and free Windows volume space. What is the target size that leaves a measured safety margin? Without that target, a shrink command can run a long time and be followed by an immediate autogrowth.
Find the File Name DBCC SHRINKFILE Needs
Use sys.database_files for logical file names, sizes, and growth settings in the current database. Check file free space through appropriate space-usage views and recent growth history. A target smaller than used data cannot be reached. Document why the file became large before changing it.
SELECT name, type_desc,
size * 8.0 / 1024 AS current_size_mb,
growth, is_percent_growth,
physical_name
FROM sys.database_files
ORDER BY file_id;The logical name is used by DBCC SHRINKFILE. This article discusses a data file; transaction log shrink follows virtual-log-file and log-reuse rules and needs a separate diagnosis. Do not shrink the wrong file because both are listed in one catalog query.
Start DBCC SHRINKFILE With TRUNCATEONLY
TRUNCATEONLY releases free space already at the end of the file without moving pages inside it. It is the least invasive first attempt after a one-time purge. If it returns enough space, stop. If not, the remaining tail contains allocated extents and reaching a smaller target requires movement.
DBCC SHRINKFILE (N'SalesData', TRUNCATEONLY);Replace SalesData with the actual logical data-file name. A target size combined with TRUNCATEONLY still cannot remove allocated tail pages. TRUNCATEONLY also stops at the file's minimum size. In my test, a nearly empty file that had just been added did not shrink at all with TRUNCATEONLY, while an explicit target size worked. I record file size before and after. A command that completes quickly but returns little space has taught us something about layout; it has not failed to do its stated job.
Understand the Slow Movement
To reach a smaller size, shrink moves allocated pages from high file locations toward free space earlier in the file. That work can be heavy for heaps, LOB allocation, large indexes, and concurrent writes. It generates I/O and can fragment indexes. A page that moves can be referenced by other structures, so the process is more than simply cutting a file at a byte offset.
Some pages cannot move at the moment, and concurrent workload can refill spaces that shrink tries to use. A long duration is not necessarily a hung command. Inspect progress, waits, and file-size changes before deciding whether to let it continue. If users are affected, pause or cancel according to the incident plan rather than waiting for a perfect target size.
Monitor DBCC SHRINKFILE Progress
sys.dm_exec_requests reports percent_complete for DBCC SHRINKFILE and an estimated_completion_time field. Microsoft labels the estimate internal-use-only, so display it as a rough hint, not an SLA. Capture command, elapsed time, wait type, and text from a second connection during the operation.
SELECT r.session_id, r.command,
r.percent_complete,
r.estimated_completion_time / 1000.0 AS estimated_seconds,
r.total_elapsed_time / 1000.0 AS elapsed_seconds,
r.wait_type, r.wait_time,
t.text AS command_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE (r.command LIKE N'DbccFilesCompact%'
OR t.text LIKE N'%SHRINKFILE%')
AND r.session_id <> @@SPID;The @@SPID test keeps the monitoring query from listing itself, since its own text contains SHRINKFILE. The command label can vary by phase and version, so also filter by the known session ID when monitoring a specific run. The percentage and estimate can stall or change as work shifts. In my lab run, the percentage climbed steadily while the estimated seconds kept growing. Save samples every few minutes with file size and user latency. A single progress snapshot cannot explain the remaining work.
Use Small Target Steps
If a smaller file is justified, choose intermediate targets such as 90 GB, 80 GB, then 70 GB rather than one large drop to 20 GB. Run one step, check duration, fragmentation, free space, and workload impact, then decide whether to continue. Each call should have a clear stop point and an operational window.
DBCC SHRINKFILE (N'SalesData', 90000);
-- Check file size and workload before a later step.
DBCC SHRINKFILE (N'SalesData', 80000);Targets are megabytes in this syntax and are illustrative. Do not paste them for a different file. Canceling a shrink can have its own cleanup cost, so plan the window. SQL Server 2022 and later accept WITH WAIT_AT_LOW_PRIORITY on DBCC SHRINKFILE, so a shrink waiting for its lock does not block other queries. Small steps give the team frequent decision points.
Measure What Happened Afterward
Check the actual file size, future autogrowth, query reads, and index fragmentation. Rebuild or reorganize only indexes whose measured fragmentation and workload justify it; automatic rebuild of every index can consume more time and log than the shrink. Set a realistic file size and fixed growth increment to prevent the next busy day from repeating the cycle.
I close the record with the one-time reason for shrink, starting and ending size, elapsed time, user impact, and expected future size. If the database will need the space again soon, keeping it inside the file is usually better. The best shrink operation can be the one stopped after TRUNCATEONLY answered the immediate disk-pressure question.
Understand Why Pages Resist Movement
Heaps, LOB chains, and large indexes can make the tail expensive to clear. A data file also has allocation structures and active pages that cannot simply be trimmed because they are free elsewhere in the file. As shrink works, concurrent inserts can claim newly free pages and change the target. This is why percent_complete can advance unevenly. Compare file-size checkpoints rather than extrapolating a precise finish from one minute of progress.
Keep Recovery Capacity in View
Shrinking is logged work and can compete with backups and production queries. Check transaction-log headroom, storage latency, and blocking while it runs. A file moved off one drive can also leave data concentrated on remaining files, changing I/O distribution. If the business reason is disk emergency, calculate how much space TRUNCATEONLY can return quickly and whether moving other files is safer than hours of page movement.
Related reading on this blog: Killing DBCC SHRINKFILE Process: Is it Safe? and Manage Database Size with DBCC SHRINKDATABASE and WAIT_AT_LOW_PRIORITY.
Shrink is not routine maintenance, it is a bounded response to a lasting space change.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
