SQL Server Autogrowth Best Practices: How to Size Data and Log File Growth

It’s the middle of the busiest hour of the day. A data file runs out of space, SQL Server starts growing it, and every session that needs to write waits. If the growth is large and the disk is slow, queries time out. If it happens in the transaction log, every write in the database waits. Nobody changed anything; an autogrowth setting chosen years ago simply went off at the worst possible moment.

This guide explains how SQL Server autogrowth works, why the old defaults and percentage growth cause trouble, how to choose sensible growth sizes for data and log files, and how to find out how often your databases are growing right now.

What Autogrowth Is, and What It Isn’t

Every SQL Server data and log file has a FILEGROWTH setting: the amount SQL Server adds to the file when it runs out of space. Autogrowth keeps a database working when it fills up unexpectedly. It isn’t meant to be the way you manage database size.

What Happens During an Autogrowth Event

• The transaction that triggered the growth waits until the growth finishes. If the growth takes long, the query can fail with a timeout.
• Log growth blocks every writer. When the transaction log grows, every other transaction that needs to write to the log has to wait until the growth completes.
• Log files are zeroed. New log space must be filled with zeros before use, so a large log growth on slow storage can take a long time. Instant file initialization only helps data files, with one exception: from SQL Server 2022, log growths of up to 64 MB can also benefit from it.
• Data files can grow almost instantly if instant file initialization is enabled for the SQL Server service account. Without it, data file growth is zeroed too.
• Many small growths fragment the files on disk and, for the log, create too many virtual log files (VLFs), which slows startup, restores, log backups and replication. See SQL Server VLF Count.

The Defaults, and Why Old Databases Have Bad Settings

Databases created on older versions keep their settings when they’re upgraded, restored or attached to a newer server. That’s why it’s common to find a 200 GB production database still growing its data file in 1 MB steps, or its log by 10%, years after an upgrade.

Why Percentage Growth Is a Trap

Percentage growth looks adaptive, but it behaves badly at both ends:

• Small files grow in tiny steps: 10% of a 50 MB log is 5 MB, so a busy day causes hundreds of growths and thousands of VLFs.
• Large files grow in huge, unpredictable steps: 10% of a 500 GB log is 50 GB that has to be zeroed while every writer waits, and the next growth is larger still.

Choosing Growth Sizes

There’s no single correct number; it depends on file size, how fast the database grows, storage speed and whether instant file initialization is enabled. Microsoft suggests a rule of thumb for testing of about one-eighth of the file size. In practice, we start from fixed MB values like these and adjust after watching real growth events:

MAXSIZE and disk space

Microsoft also recommends setting MAXSIZE on each file, so a runaway file can’t consume the whole disk and take other databases down with it. A file that hits its MAXSIZE stops growing and writes fail, so a limit only makes sense together with monitoring. Autogrowth can’t grow past the free space on the disk either: keep watching disk space, and alert well before it runs out. See SQL Server Alerts.

Multiple files in a filegroup

SQL Server spreads new data across the files of a filegroup in proportion to their free space. If one file grows and the others don’t, new writes concentrate on the file that grew and the balance is lost. Give all files in a filegroup the same size and growth settings. From SQL Server 2016, a filegroup can be set to AUTOGROW_ALL_FILES so that all its files grow together; this is the default for tempdb, while user filegroups default to AUTOGROW_SINGLE_FILE.

Check Your Current Settings

This query lists every data and log file on the instance with its size, growth setting and maximum size, and flags the settings that need attention:

SELECT DB_NAME(mf.database_id) AS database_name,
       mf.name AS logical_name,
       mf.type_desc,
       CAST(mf.size AS bigint) * 8 / 1024 AS size_mb,
       CASE WHEN mf.is_percent_growth = 1
            THEN CAST(mf.growth AS varchar(10)) + ' %'
            ELSE CAST(CAST(mf.growth AS bigint) * 8 / 1024 AS varchar(10)) + ' MB' END AS growth,
       CASE WHEN mf.max_size = -1 THEN 'UNLIMITED'
            ELSE CAST(CAST(mf.max_size AS bigint) * 8 / 1024 AS varchar(20)) + ' MB' END AS max_size,
       CASE WHEN mf.growth = 0 THEN 'Autogrowth OFF'
            WHEN mf.is_percent_growth = 1 THEN 'Percent growth'
            WHEN CAST(mf.growth AS bigint) * 8 / 1024 < 64 THEN 'Growth below 64 MB'
            ELSE '' END AS attention
FROM sys.master_files AS mf
ORDER BY attention DESC, database_name, mf.type;

Fix the Settings

FILEGROWTH can be changed online, with no downtime. This script generates the ALTER DATABASE statements for files that use percentage growth or a step below 64 MB; review the generated values before running them:

SELECT N'ALTER DATABASE ' + QUOTENAME(DB_NAME(mf.database_id))
     + N' MODIFY FILE (NAME = N''' + mf.name + N''', FILEGROWTH = '
     + CASE WHEN mf.type = 1 THEN N'512MB'                                  -- log
            WHEN CAST(mf.size AS bigint) * 8 / 1024 < 10240  THEN N'256MB'  -- data under 10 GB
            WHEN CAST(mf.size AS bigint) * 8 / 1024 < 102400 THEN N'1024MB' -- data 10-100 GB
            ELSE N'2048MB' END                                              -- data over 100 GB
     + N');' AS to_execute
FROM sys.master_files AS mf
JOIN sys.databases AS d ON d.database_id = mf.database_id
WHERE d.state_desc = N'ONLINE' AND d.is_read_only = 0
  AND mf.type IN (0, 1)
  AND mf.growth <> 0
  AND (mf.is_percent_growth = 1 OR CAST(mf.growth AS bigint) * 8 / 1024 < 64)
ORDER BY DB_NAME(mf.database_id), mf.type;

Don’t forget the model database: new databases inherit its settings, so fixing model prevents the problem from coming back. If a log has already grown in thousands of small steps, changing FILEGROWTH alone won’t remove the existing VLFs; follow the steps in our VLF guide.

How Often Are Your Databases Growing?

The default trace records every autogrowth event, including how long it took. This query reads it:

DECLARE @path nvarchar(260) =
    (SELECT REVERSE(SUBSTRING(REVERSE(path), CHARINDEX(N'\', REVERSE(path)), 260)) + N'log.trc'
     FROM sys.traces WHERE is_default = 1);

SELECT t.DatabaseName,
       t.FileName,
       CASE t.EventClass WHEN 92 THEN 'Data' WHEN 93 THEN 'Log' END AS file_type,
       t.StartTime,
       t.Duration / 1000 AS duration_ms,
       CAST(t.IntegerData AS bigint) * 8 / 1024 AS growth_mb
FROM sys.fn_trace_gettable(@path, DEFAULT) AS t
WHERE t.EventClass IN (92, 93)   -- Data File Auto Grow, Log File Auto Grow
ORDER BY t.StartTime DESC;

Many growths per day, growths during business hours, or growths that take seconds instead of milliseconds are all signs that the file is undersized or the growth step is wrong. The default trace keeps only a limited history, so check it regularly or capture the same events with Extended Events.

AUTO_SHRINK: Turn It Off

Check with SELECT name FROM sys.databases WHERE is_auto_shrink_on = 1;. For when and how a one-off shrink is appropriate, see SQL Server Shrink Best Practices.

Frequently Asked Questions