Auto-shrink is still enabled — and still a bad idea
Why AUTO_SHRINK creates fragmentation and I/O thrash, and what to do when you find it on a production database.
If a health check flags AUTO_SHRINK = ON, treat it as technical debt with a performance tax.
What auto-shrink does
SQL Server periodically shrinks files when free space exists. That sounds tidy. In practice it:
- Causes index fragmentation as pages are moved
- Generates heavy I/O at unpredictable times
- Often fights auto-growth — grow, shrink, grow, shrink
- Creates instability that looks like “random slowness”
Why it is still there
Someone enabled it years ago to “manage disk space,” then left. Disk pressure was real; the fix was wrong.
Better approaches
- Size files intentionally with growth headroom
- Use fixed autogrowth increments (MB), not tiny percent growth
- Alert on free space before emergency shrinks
- Shrink only as a planned, rare operation with a rebuild/reorg plan after
- Address the real growth source (retention, heaps, logging, bloat)
Health-check takeaway
Auto-shrink is a classic “easy win to disable” finding. Turning it off is usually simple; the deeper win is a capacity plan so nobody feels forced to enable it again.
Need hands-on SQL Server help?
Allay Data Solutions offers maintenance, tuning, and assessments from Port Elizabeth, South Africa.