Database set to Autoshrink

What this check looks for

Databases where DATABASEPROPERTYEX(name, 'IsAutoShrink') returns 1, meaning the AUTO_SHRINK option is on.

Why it matters

Autoshrink is the one database setting with essentially no defensible use, and it causes harm continuously rather than once.

When SQL Server notices a database has more than 25 percent free space in its files, a background task shrinks them. That sounds tidy. What it actually does:

It fragments every index, every time. A shrink works by taking pages from the end of the file and moving them into free space nearer the beginning. It chooses pages by physical position, not by what they contain, so index pages are put back in an order unrelated to the index. A shrink of any size routinely takes a well maintained database to near total logical fragmentation. Range scans that read sequentially now read randomly.

Then the database grows again, because the free space it removed was the room the database worked in. That growth is an autogrow event, which pauses writes while it happens.

And then it shrinks again. This is the part that makes autoshrink worse than a one-off mistake. It is a cycle:

shrink → fragment → grow → free space appears → shrink → fragment → grow

running forever, in the background, consuming I/O and CPU, and leaving the indexes in a permanently degraded state. On an instance that also runs nightly index maintenance, the rebuild defragments the indexes and creates free space, and autoshrink promptly removes the free space and refragments them. The maintenance window is spent undoing the setting’s work and the setting spends the day undoing the maintenance.

It is usually inherited rather than chosen. Somebody turned it on once on model, or a vendor installer set it, and every database created since has carried it.

How to confirm it yourself

SELECT [name],
       [is_auto_shrink_on],
       [is_auto_close_on],
       [recovery_model_desc],
       [state_desc]
  FROM sys.databases WITH (NOLOCK)
 WHERE [is_auto_shrink_on] = 1
 ORDER BY [name];

Check model too, because that is where new databases inherit it from:

SELECT [name], [is_auto_shrink_on] FROM sys.databases WITH (NOLOCK) WHERE [name] = 'model';

And see what it has already done, in the affected database:

SELECT OBJECT_NAME(ips.[object_id]) AS [table_name],
       i.[name]                     AS [index_name],
       CAST(ips.[avg_fragmentation_in_percent] AS DECIMAL(5,1)) AS [fragmentation_pct],
       ips.[page_count]
  FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
 INNER JOIN sys.indexes AS i WITH (NOLOCK)
         ON i.[object_id] = ips.[object_id] AND i.[index_id] = ips.[index_id]
 WHERE ips.[page_count] > 1000
 ORDER BY ips.[avg_fragmentation_in_percent] DESC;

The shrink events themselves are in the default trace, if it is enabled:

SELECT [DatabaseName], [StartTime], [EventClass], [Duration] / 1000 AS [duration_ms]
  FROM sys.fn_trace_gettable(
         CONVERT(NVARCHAR(500),
           (SELECT [value] FROM sys.fn_trace_getinfo(1) WHERE [property] = 2)), DEFAULT)
 WHERE [EventClass] IN (94, 95)   -- 94 data file auto shrink, 95 log file auto shrink
 ORDER BY [StartTime] DESC;

How to fix it

Turn it off. One statement, online, immediate, no downtime:

ALTER DATABASE [YourDatabase] SET AUTO_SHRINK OFF;

Across every database that has it:

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'ALTER DATABASE ' + QUOTENAME([name]) + N' SET AUTO_SHRINK OFF;' + CHAR(13)
  FROM sys.databases WITH (NOLOCK)
 WHERE [is_auto_shrink_on] = 1;
PRINT @sql;      -- read it, then run it
EXEC sp_executesql @sql;

Then repair what it has been doing:

1. Rebuild the indexes in the affected databases once, to recover what the cycle has cost.
2. Set the files to the size they actually need, so the free space autoshrink was chasing is understood as working room rather than waste.
3. Set a sensible fixed growth increment in megabytes, since the file will have been growing repeatedly and may have a poor growth setting too.
4. Fix model, or new databases keep arriving with it on.

If you genuinely need to reclaim space once, after archiving a large amount of data permanently, do it deliberately with DBCC SHRINKFILE on the specific file, shrink to a size that leaves working room, and rebuild the indexes afterwards. What is never right is leaving a background task to decide.

How long it takes

About two hours: seconds for the setting, and the rest rebuilding the indexes it has fragmented.

Related reports

Related checks

Frequently asked questions

We are short of disk space and it helps. It does not, on any timescale longer than a few hours. The space comes back because the database needs it, and each cycle costs fragmentation. Archiving data is the fix for a space problem.

Is it safe to turn off with users connected? Yes. It is a metadata change and takes effect immediately.

Does autoshrink affect the log file too? Yes, and there it produces very high VLF counts, which slow startup, restores and log backups.

Is there any case for it? None that survives scrutiny. Microsoft’s own guidance has recommended against it for two decades.