SQL Server DBCC SHRINKDATABASE: When and When Not to Shrink

calendar Jun 5, 2026 / · 6 min read · sql server database maintenance index fragmentation disk space database administration maintenance scripts sql scripts dbcc commands DBCC SHRINKFILE sys.database_files sys.dm_db_index_physical_stats sys.indexes  ·
Share on: linkedin copy

A one-time data load doubled a data file overnight, the load is done, the rows are deleted, and the file is now mostly empty space. The reflex — reach for DBCC SHRINKDATABASE and give the disk back — is a common way to hurt performance without meaning to. This post explains what shrinking does to your indexes, when it is genuinely the right call, and how to reclaim space the few times you must without wrecking performance.

Purpose and Overview

Shrinking a data file moves assigned pages from the end of the file to unassigned pages near the front. Microsoft's documentation states that a shrink "doesn't preserve the fragmentation state of indexes in the database, and can increase index fragmentation, which might reduce read I/O throughput for queries using large scans." The move is driven by position in the file, not by logical index order, so the contiguous page chains that range scans depend on can end up scattered. You reclaim disk space and may pay for it in fragmentation on large indexes.

DBCC SHRINKDATABASE applies this operation to every file in a database at once; DBCC SHRINKFILE targets a single file. Both accept the TRUNCATEONLY option, which releases free space at the end of the file without moving pages. Both exist for legitimate reasons, but those reasons are narrow: a genuine, permanent reduction in data volume (Microsoft notes a shrink is most effective after a TRUNCATE TABLE or DROP TABLE), a server being decommissioned or downsized, or emptying a file with EMPTYFILE so it can be removed. None of those describe routine maintenance, which is exactly where shrink most often gets misused.

The crucial distinction is between trailing free space and interior free space. If the empty space sits at the end of the file, you can release it cheaply with no page movement at all. If it is interspersed through the file, reclaiming it requires page relocation — and even then, autogrowth will simply re-grow the file the next time the data expands, so you have risked fragmentation for nothing.

Code Breakdown

Before shrinking anything, measure how much free space actually exists and where. Then prefer the TRUNCATEONLY form, which is non-destructive.

 1-- 1. How much free space per file, and is it worth reclaiming?
 2SELECT
 3    f.name                                              AS logical_name,
 4    f.type_desc,
 5    CAST(f.size / 128.0 AS decimal(10,2))               AS size_mb,
 6    CAST(FILEPROPERTY(f.name, 'SpaceUsed') / 128.0
 7         AS decimal(10,2))                              AS used_mb,
 8    CAST((f.size - FILEPROPERTY(f.name, 'SpaceUsed')) / 128.0
 9         AS decimal(10,2))                              AS free_mb
10FROM sys.database_files AS f;
11
12-- 2. NON-DESTRUCTIVE: release only trailing free space. No page movement.
13--    Try this first.
14DBCC SHRINKFILE (N'YourDatabase_Data', TRUNCATEONLY);
15
16-- 3. DISRUPTIVE: shrink to a target size, relocating pages, which
17--    can fragment indexes. Use only when truly justified.
18DBCC SHRINKFILE (N'YourDatabase_Data', 4096);   -- target 4096 MB

The free-space query

FILEPROPERTY(name, 'SpaceUsed') returns used pages, and sys.database_files.size returns total allocated pages; both are in 8 KB pages, so dividing by 128 converts to megabytes. The free_mb column tells you whether there is enough reclaimable space to justify any action at all — if it is small, stop here.

SHRINKFILE vs SHRINKDATABASE

SHRINKDATABASE shrinks every data and log file in the database, which is rarely what you want. SHRINKFILE lets you target the one file that genuinely has reclaimable space and give it a precise target size in megabytes. Both commands accept TRUNCATEONLY, but with SHRINKDATABASE you cannot pick the file. Prefer SHRINKFILE.

TRUNCATEONLY

DBCC SHRINKFILE (name, TRUNCATEONLY) releases only the free space at the physical end of the file back to the operating system. Microsoft's documentation says it "doesn't perform any page movement inside the file," so it does not reshuffle index pages the way a targeted shrink does. If the free space happens to be trailing, for example after a TRUNCATE TABLE or DROP TABLE, this returns the disk space cheaply. It is the least disruptive shrink form.

The Fragmentation Cost

The targeted-size form in step 3 is where the risk lives. Relocating pages to compact the file can shuffle them out of logical order, so index range scans that used to read contiguous pages may chase scattered ones. Microsoft also warns that if a file keeps growing back after repeated shrinks, "repeatedly shrinking the database file is counterproductive," because the growth needed to allocate new space "can hinder performance."

You can measure the effect on your own data. On a test copy, capture avg_fragmentation_in_percent from sys.dm_db_index_physical_stats before and after a targeted shrink and compare the largest indexes:

1SELECT i.name AS index_name,
2       ps.avg_fragmentation_in_percent,
3       ps.page_count
4FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ps
5JOIN sys.indexes AS i
6     ON i.object_id = ps.object_id AND i.index_id = ps.index_id
7WHERE ps.page_count > 1000
8ORDER BY ps.avg_fragmentation_in_percent DESC;

That difference is the cost you are accepting. If you must shrink, do any index maintenance after the shrink, not before, or the shrink can undo it.

Key Benefits and Use Cases

  • Permanent data reduction — after archiving, truncating or dropping a large fraction of a database for good, a one-time shrink right-sizes the files.
  • Decommissioning and downsizing — reclaiming space on a server being retired or migrated to smaller storage.
  • File removal — DBCC SHRINKFILE with EMPTYFILE migrates data to other files in the same filegroup so the emptied file can be removed.
  • Trailing-space reclaim — TRUNCATEONLY returns end-of-file free space without moving pages.
  • Emergency disk relief — as a stopgap when a volume is critically full and no other space exists, accepting the fragmentation as a known debt.
  • Log file correction — SHRINKFILE on a transaction log that ballooned during an unusual operation, once the log has been truncated (for example by a log backup).

Performance Considerations

  • Index fragmentation: the page-relocation forms can increase fragmentation and reduce read I/O throughput for large scans.
  • Resource cost: Microsoft describes shrink as a long-running and resource-intensive operation.
  • Blocking: shrink needs a schema modification (Sch-M) lock when moving or deleting IAM pages, which can block the Sch-S locks user queries need. SQL Server 2022 and later add WAIT_AT_LOW_PRIORITY to reduce this.
  • Autogrowth undoes it: if the data grows back, the file re-grows, so you fragmented indexes to reclaim space you immediately lost.
  • Churn: routine shrink-and-grow cycles waste I/O and keep indexes perpetually fragmented.

Practical Tips

  • Never schedule shrink as routine maintenance — it is a deliberate, justified one-off, not a nightly job.
  • Never put SHRINK after a rebuild in a maintenance plan — the shrink can increase fragmentation in the indexes you just defragmented.
  • Prefer TRUNCATEONLY — try the non-destructive form first; it reclaims trailing free space without moving pages.
  • Check fragmentation after a necessary shrink — if you had to relocate pages, re-run the fragmentation query and rebuild or reorganize only the indexes that need it.
  • Right-size files instead — set sensible initial sizes and autogrowth increments so files stop ballooning in the first place.

Conclusion

DBCC SHRINKDATABASE and SHRINKFILE are emergency and decommissioning tools, not maintenance tools. Measure first, prefer TRUNCATEONLY, and when a targeted shrink is genuinely warranted, check index fragmentation right after. For the full toolkit, see the complete DBCC command reference.

References

Posts in this series