How to Shrink SQL Server Database Files in Chunks already says it plainly in its common pitfalls: shrinking causes index fragmentation, every time. That’s correct, and worth taking on faith from someone who’s had to clean up after it. It’s more useful with a real number attached. This post runs the actual before/after: a lab table, a realistic purge, a shrink, and a sys.dm_db_index_physical_stats reading on either side of it.
If you haven’t read the chunked-shrink post, start there for the technique itself, safely bringing a large file down in bounded steps. This post picks up exactly where its pitfalls section left off and puts a number on the claim.
The Setup: A Realistic Purge Shape
Fragmentation from a shrink isn’t caused by shrinking itself as an abstract operation, it’s caused by which rows are still alive relative to where they physically sit in the file. The realistic case that actually produces it: an archive or purge job deletes the oldest rows first. Those rows are physically near the front of the file (they were inserted first), so deleting them frees space at the front while surviving, newer rows stay physically positioned toward the end.
CREATE TABLE dbo.FragTest (id INT IDENTITY PRIMARY KEY CLUSTERED, filler CHAR(800));
-- 20,000 rows inserted, then the oldest 15,000 purged, exactly the archive/purge shape
DELETE FROM dbo.FragTest WHERE id <= 15000;
5,000 rows survive, physically sitting toward the end of a file that’s now mostly empty space at the front.
Before the Shrink
SELECT index_id, index_type_desc, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.FragTest'), NULL, NULL, 'DETAILED');
| Fragmentation | Page Count | |
|---|---|---|
| Clustered index, before shrink | 0.17% | 1,181 |
Essentially unfragmented. The delete freed space but didn’t move anything, the surviving rows are still sitting exactly where they were originally written, in the same physical order as the clustered key.
After the Shrink
DBCC SHRINKFILE ('FragDemo', 6);
Same query, same table, immediately after:
| Fragmentation | Page Count | |
|---|---|---|
| Clustered index, after shrink | 99.82% | 558 |
From 0.17% to 99.82% from a single shrink operation. The mechanism is exactly what the chunked-shrink post describes: DBCC SHRINKFILE reclaims the empty space at the front by moving the surviving pages from the end of the file backward into it. Each moved page gets slotted into whatever gap is available, not into an order that preserves the clustered key’s logical sequence. The result is a page chain that’s now almost entirely out of physical order relative to the index it belongs to, which is exactly what avg_fragmentation_in_percent measures.
Page count also dropped (1,181 → 558), consistent with the file actually consolidating what the delete alone had only marked as free, this is the shrink doing real, intended work. The fragmentation is the side effect of how it does that work, not a sign anything went wrong.
After the Rebuild
ALTER INDEX ALL ON dbo.FragTest REBUILD;
| Fragmentation | Page Count | |
|---|---|---|
| Clustered index, after rebuild | 0.0% | 556 |
Back to fully unfragmented. This is the step the chunked-shrink post already calls out as expected and necessary, “plan to rebuild or reorganize affected indexes after a shrink completes.” The number above is what makes that instruction concrete: without the rebuild, a query relying on ordered clustered-index scans is reading a page chain that’s 99.82% out of physical order.
What This Means in Practice
- A shrink without a follow-up rebuild is an incomplete operation, not a complete one. The pitfall isn’t hypothetical or occasional, this test went from 0.17% to 99.82% on the very first shrink, on a lab table with no special conditions.
- The larger the file and the more scattered the surviving data, the worse the resulting fragmentation tends to be. A small lab table hit nearly 100%; a genuinely large production file with data scattered unevenly through it can behave the same way.
- This is exactly why the chunked-shrink technique matters even for fragmentation, not just for I/O safety. Rebuilding indexes after each chunk, or at least after the whole operation, is not optional maintenance, it’s the second half of the same job.
- Check fragmentation as a routine part of any shrink, not an afterthought. Get Index Fragmentation run before and after a shrink turns “shrinking causes fragmentation” from a known fact into a specific number for the database actually being shrunk.
Best Practices
- Never treat a shrink as finished until the affected indexes have been rebuilt or reorganized, budget the time and I/O for that step when planning a shrink, it’s part of the operation, not separate from it.
- Run Get Index Fragmentation immediately after any shrink to see the real number for that specific database, don’t assume it based on a lab test elsewhere, including this one.
- Use
REORGANIZEinstead ofREBUILDfor very large indexes where a full rebuild’s exclusive lock (Enterprise Edition’s online rebuild aside) isn’t acceptable during the maintenance window; it’s slower per pass but doesn’t require the same locking. - Combine this with the chunked shrink approach for large files: shrink in bounded steps, then rebuild once at the end rather than leaving a large index sitting fragmented for longer than necessary.
Related Scripts
- How to Shrink SQL Server Database Files in Chunks, the technique this post’s fragmentation numbers are measuring the pitfall of
- Get Index Fragmentation, run this immediately after any real shrink to get the actual number
- Index Maintenance (pillar), the wider set of scripts covering fragmentation, missing indexes, and rebuild scheduling
- Get Database Growth Risk and Forecast, to check whether a shrink is even the right call before reaching for one
Leave a Reply