Quick Scan Report – File Growth Too Small

What this check looks for

Files with a fixed growth increment, is_percent_growth = 0, where that increment is less than 640 pages, which is 5 MB. It applies to data files and log files alike.

Why it matters

A file that grows in tiny steps grows constantly, and every growth is a pause.

The classic case is the SQL Server default of 1 MB for a data file. A database taking on 1 GB of data does it through a thousand separate growth events, each one a metadata change, each one a moment when writes to that file wait. On a busy system those pauses are frequent enough to show up as general sluggishness with no single query to blame.

On a log file it is worse, because of VLFs. SQL Server creates a set of virtual log files inside each growth increment, and the number is derived from the size of the increment. Very small growths produce enormous numbers of very small VLFs, and this is the single most common way a log file ends up with tens of thousands of them. The consequences of a high VLF count are:

• Slow database startup, because recovery walks every VLF.
• Slow restores, for the same reason, which matters most in the worst moment.
• Slow log backups.
• Log truncation that behaves oddly, because the log can only be truncated at VLF boundaries.

The small growth setting also tends to travel. It is inherited from model, so every database created on the instance gets it, and it survives every migration because the file settings move with the database.

How to confirm it yourself

SELECT DB_NAME(mf.[database_id])                     AS [database_name],
       mf.[name]                                     AS [logical_name],
       mf.[type_desc],
       CAST(mf.[size] * 8.0 / 1024 AS DECIMAL(12,1)) AS [size_mb],
       CASE WHEN mf.[is_percent_growth] = 1
            THEN CAST(mf.[growth] AS VARCHAR(10)) + ' %'
            ELSE CAST(mf.[growth] * 8 / 1024 AS VARCHAR(10)) + ' MB' END AS [growth],
       mf.[physical_name]
  FROM sys.master_files AS mf WITH (NOLOCK)
 WHERE mf.[is_percent_growth] = 0
   AND mf.[growth] < (128 * 5)      -- under 5 MB
 ORDER BY mf.[size] DESC;

Then look at what it has already cost you, in the database named in the finding:

DBCC LOGINFO;   -- one row per VLF; a few hundred is normal, thousands is not

or, on SQL Server 2016 SP2 and later:

SELECT COUNT(*) AS [vlf_count]
  FROM sys.dm_db_log_info(DB_ID());

And how often growth is actually happening:

SELECT [DatabaseName], [FileName], [StartTime], [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 (92, 93)
 ORDER BY [StartTime] DESC;

Dozens of rows in a day is the confirmation.

How to fix it

Set a sensible fixed growth:

ALTER DATABASE [YourDatabase]
  MODIFY FILE ( NAME = N'YourLogicalFileName', FILEGROWTH = 512MB );

Then size the file for what it needs, so autogrowth stops being part of normal operation:

ALTER DATABASE [YourDatabase]
  MODIFY FILE ( NAME = N'YourLogicalFileName', SIZE = 100GB );

If the log file already has a high VLF count, changing the growth does not fix it. The existing VLFs stay. Clearing them is a separate, deliberate operation: back up the log, shrink the log file right down, then grow it back in a few large steps to the size it should be. That is the one legitimate use of shrinking a log file, and it is done once rather than on a schedule.

Fix model as well, or every new database on this instance arrives with the same setting.

How long it takes

About an hour. Rebuilding a badly fragmented log adds time, and it needs a quiet moment rather than a maintenance window.

Related reports

Related checks

Frequently asked questions

Why 5 MB as the line? It is low enough that nothing sensibly configured falls below it, and high enough to catch the 1 MB and 10 percent-of-a-tiny-file defaults that cause the trouble.

Does this matter if the database never grows? Then it costs nothing today. It is still worth fixing, because the setting is what you will be relying on the day the database does grow.

Is there a downside to a large growth increment? On a data file with instant file initialization, effectively none. Without it, and on every log file, a very large increment means a longer single pause. That is the trade: fewer, longer pauses instead of constant short ones, and fewer is better.

Our VLF count is already in the thousands. Changing the growth setting stops it getting worse but does not undo it. Shrink the log right down once, then grow it back in a few large steps.