Autogrowth Settings: Why 1 MB and 10 Percent Still Hurt

A database file should not need emergency expansion for every small burst of work. Review autogrowth settings with current file size and growth history so the fallback mechanism stops acting as the normal capacity plan.

Inventory Autogrowth Settings for Every Data and Log File

Growth configuration belongs to each file. Reviewing only the database's primary data file misses log files and additional data files. Start with a complete inventory and distinguish fixed growth from percentage growth before comparing the numeric value. The same value in the growth column can describe different units.

SELECT DB_NAME(database_id) AS DatabaseName,name AS LogicalFileName,
       type_desc,physical_name,size*8.0/1024 AS CurrentSizeMB,
       is_percent_growth,
       CASE WHEN is_percent_growth=1 THEN growth END AS GrowthPercent,
       CASE WHEN is_percent_growth=0 THEN growth*8.0/1024 END AS GrowthMB,
       max_size
FROM sys.master_files
WHERE type IN (0,1)
ORDER BY DatabaseName,type_desc,name;

I review the file's workload and remaining storage capacity beside this output. An apparently reasonable increment cannot help when the volume is full or the configured maximum size blocks the next growth. The max_size value uses metadata conventions, including special values, so interpret it rather than presenting the raw integer as megabytes.

A zero growth value disables automatic expansion. That can be intentional for tightly managed files, but it requires reliable advance capacity management. Check the operational process before changing it. An inventory should expose the accepted policy and any gap in that policy, not automatically enable growth everywhere.

Understand Why Percentage Increments Drift

A percentage increment scales with the file's current size. As the file grows, the next requested extension becomes larger. That makes the pause and capacity demand less predictable than a deliberate fixed increment. Ten percent of a small file and ten percent of a very large file are different operational requests.

Use autogrowth settings that fit the actual file and workload rather than copying a historical default. A percentage can also round according to file-growth rules, so do not infer exact allocation from a casual mental calculation. Retain the actual growth events and resulting file sizes when assessing what happened.

Changing the increment does not enlarge the file immediately. It changes future automatic extensions. If the current file is already close to its required capacity, plan a separate approved initial-size change. A better emergency setting is useful, but a scheduled capacity allocation is still easier to manage.

Avoid Repeated Tiny Extensions

A one-megabyte increment can require many extensions during a large load or maintenance operation. Each extension adds allocation work and can interrupt progress. Repeated data-file expansion can also contribute to storage fragmentation, while repeated small log expansion can produce an undesirable virtual-log-file layout.

The exact effect depends on the engine version, storage, and initialization behavior. Do not equate every tiny data growth with a guaranteed amount of physical fragmentation or every log growth with the same number of VLFs. Inspect the actual file layout and growth history before choosing a corrective action.

Small increments look economical until the workload asks for them repeatedly. The file has not learned to grow efficiently; it has learned to ask permission very frequently. Pre-size for expected work and keep automatic growth as a controlled fallback rather than an hourly operating procedure.

Read Retained Growth Events From the Default Trace

Where the default trace is available and enabled, its retained files include data and log autogrowth events. Locate its current path through the catalog rather than hard-coding an installation directory. The example uses event classes ninety-two and ninety-three and converts their page increment to megabytes.

DECLARE @TracePath nvarchar(260)=
    (SELECT path FROM sys.traces WHERE is_default=1);
IF @TracePath IS NULL
    SELECT N'No default trace is currently available.' AS InspectionStatus;
ELSE
    SELECT StartTime,EndTime,DatabaseName,FileName,
           CASE EventClass WHEN 92 THEN N'Data growth'
                           WHEN 93 THEN N'Log growth' END AS GrowthType,
           IntegerData*8.0/1024 AS GrowthMB
    FROM sys.fn_trace_gettable(@TracePath,DEFAULT)
    WHERE EventClass IN (92,93)
      AND StartTime>=DATEADD(DAY,-7,GETDATE())
    ORDER BY StartTime DESC;

The date filter requests recent retained events; it does not guarantee seven complete days of history. Rollover can remove older events, and a disabled trace supplies no coverage. SQL Trace is deprecated, so use an approved Extended Events collection for durable ongoing evidence. Report unavailable history as unavailable rather than zero growth.

Inspect the Log's Virtual File Population

A transaction log contains VLFs whose layout depends on prior allocations and version-specific creation rules. Inspect them before concluding that a growth setting needs a shrink-and-regrow operation. The following current-database query uses sys.dm_db_log_info, available from SQL Server 2016 Service Pack 2.

SELECT f.name AS LogicalFileName,COUNT_BIG(*) AS VirtualLogFiles,
       SUM(CASE WHEN l.vlf_active=1 THEN 1 ELSE 0 END) AS ActiveVirtualLogFiles
FROM sys.dm_db_log_info(DB_ID()) AS l
JOIN sys.database_files AS f ON f.file_id=l.file_id
GROUP BY f.name;
SELECT name,log_reuse_wait_desc FROM sys.databases WHERE database_id=DB_ID();

I investigate why log space cannot be reused before changing its growth policy. A long transaction, backup gap, or other reuse wait can keep a log expanding despite a reasonable increment. Fixing that cause is different from choosing how much space the next expansion requests.

SQL Server 2022 allows log growth events up to sixty-four megabytes to benefit from instant file initialization. Larger events do not receive that benefit. Treat this version-specific behavior as one input to the decision, not a reason to force every log into the smallest possible increments regardless of its workload.

Set Fixed Autogrowth Settings With Explicit File Names

Choose a fixed increment large enough to avoid repeated growth during ordinary bursts and small enough for the accepted capacity and pause budget. Data and log files can need different values. The example creates a demonstration database, whose default logical file names are GrowthLab and GrowthLab_log. It uses proposed increments for rehearsal.

IF DB_ID(N'GrowthLab') IS NULL
    CREATE DATABASE GrowthLab;
GO
ALTER DATABASE GrowthLab
MODIFY FILE (NAME=N'GrowthLab',FILEGROWTH=256MB);
ALTER DATABASE GrowthLab
MODIFY FILE (NAME=N'GrowthLab_log',FILEGROWTH=64MB);

Replace those names only after checking the target inventory. The values are example configuration inputs, not measured optimums. Document the proposed growth demand against current file size, available volume space, and typical workload growth. A configuration that fits a small lab does not automatically fit a busy large database.

Pre-Size for the Work You Already Expect

Allocate accepted capacity before a planned load or substantial maintenance operation. Keep room for the file itself, backups or other colocated files, and an operational reserve. Size changes should be reviewed against actual storage availability, not only the file's configured maximum.

ALTER DATABASE GrowthLab
MODIFY FILE (NAME=N'GrowthLab',SIZE=2048MB);
ALTER DATABASE GrowthLab
MODIFY FILE (NAME=N'GrowthLab_log',SIZE=512MB);

These statements demonstrate planned enlargement for a lab whose current files are smaller than the requested sizes. They do not shrink larger files. Verify the current sizes first, then choose accepted targets. Avoid routine shrink-and-grow cycles, which discard reusable capacity and recreate allocation work the next time it is needed.

Verify Autogrowth Settings Through the Next Cycle

Which operation creates the largest accepted space demand? Include it in the observation period along with routine traffic. Re-read file settings, check free space, and review new growth events after the change. Confirm that the log's reuse conditions and backup process remain healthy.

Keep autogrowth settings in a recurring capacity review. The useful outcome is fewer unplanned extensions and a predictable fallback. Keep evidence that the chosen increments fit the current workload and storage, rather than a copied rule about every database.

Related reading on this blog: Monitoring Database Autogrowth Settings and Monitoring Log Growth and VLF Counts.

Autogrowth is not a capacity plan, it is a fallback whose size and limits need deliberate management.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.