Default Maintenance Plan Shrink Database

What this check looks for

The definitions of the maintenance plan packages stored on this instance are read, and the check looks for the Shrink Database task, which appears in the package XML as Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceShrinkTask.

The check is skipped on Amazon RDS, where the package store is not reachable in the same way.

Why it matters

Of everything the maintenance plan designer offers, this is the one task that reliably makes a database worse. The others on this list are inefficient. This one is actively destructive.

It fragments every index in the database. Shrinking a data file works by taking pages from the end of the file and moving them into free space nearer the beginning. It picks pages by where they are in the file, not by what they are, so it puts the index pages back in an order that has nothing to do with the index. A shrink of any size routinely takes a well maintained database to near total logical fragmentation. Range scans that read sequentially before the shrink read randomly afterwards.

The space it releases is space the database is about to take back. A database that used 100 GB yesterday will use 100 GB again. The free space inside a data file is not waste, it is the room the database works in. Shrinking it means the file has to autogrow again, which pauses writes while it happens, and on a log file the shrink and regrow cycle is how VLF counts get into the tens of thousands.

It is slow, and it holds locks while it runs. A shrink on a large database can run for hours, generating enormous amounts of log as it moves pages, and every page it moves is logged.

So the usual sequence on an instance with this task scheduled is: shrink fragments the indexes, the index rebuild step later in the same plan defragments them again, the rebuild grows the file back, and the next night’s shrink undoes it. The plan spends the maintenance window fighting itself, and generates a full night of transaction log every time.

How to confirm it yourself

SELECT p.[name]        AS [plan_name],
       sp.[subplan_name],
       j.[name]        AS [job_name],
       j.[enabled]
  FROM msdb.dbo.sysmaintplan_plans AS p WITH (NOLOCK)
 INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp WITH (NOLOCK)
         ON sp.[plan_id] = p.[id]
  LEFT JOIN msdb.dbo.sysjobs AS j WITH (NOLOCK)
         ON j.[job_id] = sp.[job_id]
 ORDER BY p.[name], sp.[subplan_name];

Then open the plan in the maintenance plan designer and look for the Shrink Database task, or search the stored package definition for DbMaintenanceShrinkTask.

Check whether the damage is already visible:

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
   AND ips.[avg_fragmentation_in_percent] > 30
 ORDER BY ips.[avg_fragmentation_in_percent] DESC;

How to fix it

Remove the task from the plan. That is the whole fix, and it can be done with no downtime and no maintenance window.

Open the maintenance plan, delete the Shrink Database task, reconnect the remaining tasks in the workflow so nothing is orphaned, and save.

Then, once:

• Rebuild the indexes the shrink has already fragmented, to recover what was lost.
• Check the autoshrink setting on every database, which is the same mistake made a different way and has its own check.
• Decide what the file sizes should actually be and set them there, rather than letting a nightly shrink and daily autogrow decide.

When shrinking is legitimate, and it sometimes is, it is a one off operation done by a person: after archiving a large amount of data permanently, or after moving a table to another filegroup. In that case use DBCC SHRINKFILE targeting the specific file, shrink to a size that leaves working room, and rebuild the indexes afterwards. What is never right is putting it on a schedule.

How long it takes

About two hours: minutes to remove the task, and the rest to rebuild the indexes it has already fragmented.

Related reports

Related checks

Frequently asked questions

We need the disk space back. Then the question is why the database grew, and archiving or moving data is the answer. Shrinking gives the space back to the volume and the database takes it again within days, having fragmented itself in the process.

Is shrinking the log file also bad? It causes different damage. It does not fragment indexes, but repeated shrink and grow cycles produce very high VLF counts, which slow startup, restores and log backups. And if the log grew for a reason, shrinking it does not address the reason.

What if we shrink with TRUNCATEONLY? DBCC SHRINKFILE (file, TRUNCATEONLY) only releases free space at the end of the file and does not move pages, so it does not fragment anything. It is the safe variant and it is not what the maintenance plan task does.

The plan has run for years without a problem. It has been fragmenting indexes for years. Whether that has been a problem depends on whether anything measured it. Look at the fragmentation query above before concluding it has been harmless.