A data file shrink reduces an individual database data file by relocating pages and releasing unused space to the operating system. Avoid it as routine maintenance: shrinking can consume substantial resources, block applications, and fragment indexes. section 9.2 section 9.1
Mistake: reclaiming space without planning what follows
MarketingArchive retained nine years of historical data although the business required only three. After purging older rows, a one-time shrink was reasonable because it reclaimed unnecessary Azure storage. The DBA ran it during a maintenance window, but users later reported slower queries because the shrink fragmented indexes. Establish a fragmentation baseline, purge obsolete data, estimate free space, shrink only when the space will not be needed again, and rebuild affected indexes afterward. section 9.2
Apply these rules before shrinking a data file:
- Do not enable autoshrink to respond to a space warning; its recurring background operations can acquire locks, consume CPU and disk, and heavily load the transaction log. section 9.1
- Treat manual shrinking as an exception, typically after permanently removing data and during a planned maintenance window. section 9.2
- Expect page movement during the operation to affect index organization; measure fragmentation before and rebuild indexes after the shrink when required. section 9.2
