Quick Scan Report – Percent Growth

What this check looks for

Any file in sys.master_files with is_percent_growth = 1. It applies to both data files and log files, and the message names the database and the logical file.

Why it matters

Percentage growth compounds, and that is the whole problem.

A 10 percent growth setting sounds modest and stays modest for a while. The arithmetic does not:

File size Each growth event Roughly how long it blocks
1 GB 100 MB barely noticeable
10 GB 1 GB seconds
100 GB 10 GB tens of seconds, or minutes on a log file
500 GB 50 GB long enough to look like an outage

A growth event is not free and it is not background work. Writes to the file wait while it happens. For a data file with instant file initialization enabled the new space is allocated without being zeroed, which makes it fast. For a log file it is never fast, because log files are always zeroed, with no exception and no setting to change it. A 50 GB log growth writes 50 GB of zeros while every transaction on that database waits.

Two things follow:

  • The problem hides until it is severe. Everything works fine for years while the file is small, and the first symptom is an unexplained multi-minute stall on a database that has grown.
  • It gets worse on its own. Every growth makes the next growth bigger. There is no point at which it settles.

The same setting is also how log files acquire enormous VLFs. SQL Server decides the number of virtual log files from the size of each growth increment, so very large growths produce very large VLFs, which makes log backups and recovery behave badly in their own way.

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],
       CASE WHEN mf.[is_percent_growth] = 1
            THEN CAST(CAST(mf.[size] * 8.0 / 1024 * mf.[growth] / 100.0 AS BIGINT) AS VARCHAR(20)) + ' MB'
            ELSE '' END                                     AS [next_growth_would_be],
       mf.[physical_name]
  FROM sys.master_files AS mf WITH (NOLOCK)
 WHERE mf.[is_percent_growth] = 1
 ORDER BY mf.[size] DESC;

The next_growth_would_be column is the one to read. It turns an abstract percentage into the number of megabytes the database will stop for, next time.

And how often it has been happening, from the default trace:

SELECT [DatabaseName], [FileName], [StartTime], [Duration] / 1000 AS [duration_ms], [EventClass]
  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)   -- 92 data file autogrow, 93 log file autogrow
 ORDER BY [StartTime] DESC;

How to fix it

Change the growth to a fixed size in megabytes. The change is immediate, takes no downtime and no locks:

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

Reasonable starting points, adjusted for how fast the database actually grows:

File size Fixed growth
Under 10 GB 128 MB to 256 MB
10 to 100 GB 512 MB
Over 100 GB 1 GB

Then do the more useful thing: size the file properly. Autogrowth is a safety net for the unexpected, not a capacity plan. A file that autogrows every week was sized wrongly, and every growth event is a small outage you scheduled by accident.

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

Check instant file initialization before growing a large data file, or the growth will zero every byte. It is a Windows privilege, Perform volume maintenance tasks, granted to the SQL Server service account, and it has its own check in this report.

Check model too. New databases inherit their growth settings from it, so a model database with percentage growth keeps producing new databases with the same problem.

How long it takes

About an hour across an instance. The statements are instant; deciding the right sizes is the work.

Report Why you would go there
Files Every file with its size, growth setting and free space.
File Size Over Time How fast each file has actually grown.
Disk Space Forecast Whether the drive can take the size you are planning.
VLFs The log file damage that large percentage growths cause.
File Utilization How full each file is, which tells you what to size it to.
Check
File growth too small The opposite mistake, growing far too often.
A data or log file cannot grow any further Growth turned off entirely, or a maximum size reached.
High VLF count What large log growths leave behind.
Instant file initialization not enabled What makes a data file growth fast or slow.

Frequently asked questions

Is 1 percent safe? It is safer than 10, and it is still a percentage, so it still compounds and it still gets worse as the file grows. A fixed size in megabytes is simply the right answer.

Does this apply to tempdb? Yes, and it matters there because tempdb is recreated at every restart and grows from its configured size under load. Fixed growth and correct sizing both apply.

Why is percent growth the default? It is the historical default from a time when databases were much smaller, and it has survived because changing a default breaks upgrades. model on most instances still carries it.

We have autogrowth off instead. That is a different finding with its own check, and it is usually worse. A file that cannot grow stops the database when it fills.