What’s the issue?
AUTO_SHRINK is a database option that, when enabled, causes SQL Server to periodically check for free space in data and log files and automatically shrink them when more than 25 percent of the file is unused. The setting is off by default for new databases but is often found enabled on older databases, databases created from templates, or those restored from legacy systems.
Why is this a problem?
Auto shrink is widely regarded as one of the most harmful settings in SQL Server. The shrink process generates significant I/O and causes severe index fragmentation, which degrades query performance and increases the workload on your storage. Worse, shrink and growth operations often occur in a damaging cycle: the file shrinks, the database grows again to accommodate normal activity, and the cycle repeats. This wastes resources continuously and can happen at unpredictable times, including during peak business hours. Auto shrink also runs with no awareness of workload, so it can kick off in the middle of critical operations.
What should you do about this?
Identify affected databases by querying sys.databases where is_auto_shrink_on = 1. Disable the setting on each one using ALTER DATABASE [DatabaseName] SET AUTO_SHRINK OFF;. If a database has already been affected, rebuild indexes during a maintenance window to clean up the fragmentation caused by prior shrink operations.
Size data and log files appropriately based on actual growth patterns so you do not need to reclaim space routinely, and if you must shrink a file in response to a one time event (such as purging a large archive), do so manually and deliberately rather than relying on automation.