Why SQL Server Database Auto-Growth Settings Matter More Than You Think

Poorly configured SQL Server database auto-growth settings are one of the most common and easily fixed performance problems we encounter during health checks. The default auto-growth increment of 1MB (or percentage-based growth) causes two serious problems: excessive Virtual Log File (VLF) fragmentation that slows database recovery, and autogrowth wait events that stall write operations while SQL Server extends the data file. Fixing this across all databases takes about five minutes with the right script.

This article explains the problem, the recommended fix, and provides a ready-to-run T-SQL script that analyses every database on your instance and generates the correct ALTER DATABASE statements automatically.

What Goes Wrong With Default Auto-Growth Settings?

SQL Server ships with auto-growth enabled by default, which is sensible. But the default increment sizes are not sensible for any production environment.

There are two specific failure modes worth understanding.

VLF fragmentation. The transaction log is divided internally into Virtual Log Files. When auto-growth fires in small increments, SQL Server creates a large number of small VLFs. A transaction log with hundreds or thousands of VLFs takes significantly longer to recover at startup, and can degrade log backup and restore performance. Microsoft's guidance recommends keeping VLF counts manageable, typically under 50 for most databases, and using fixed-size growth increments to achieve this.

Autogrowth wait events. When a data file needs to extend, SQL Server has to allocate and initialise the new space before the write can proceed. If Instant File Initialisation (IFI) is enabled, data file growth is fast. If it isn't, or if you're growing the log file (which IFI doesn't help), every autogrowth event introduces a measurable wait. Small, frequent growth events compound this problem. You'll see this show up as PREEMPTIVE_OS_WRITEFILEGATHER waits in your wait stats.

Percentage-based growth is particularly dangerous. A 10% growth increment on a 500GB database means SQL Server tries to allocate 50GB in a single event. That's a long wait, and it can exhaust available disk space unexpectedly.

There's no single universally correct value, but the following size-based thresholds are a practical and widely used starting point for SQL Server database auto-growth configuration:

  • Databases under 300MB: 50MB growth increment
  • Databases between 300MB and 1GB: 100MB growth increment
  • Databases between 1GB and 2GB: 200MB growth increment
  • Databases over 2GB: 400MB growth increment

These values balance two competing concerns. You want the increment large enough to avoid frequent growth events and excessive VLF creation, but not so large that a single autogrowth event monopolises disk I/O or exhausts free space unexpectedly.

For very large databases (say, 500GB or more), you may want to increase the 400MB ceiling further, or better still, pre-size the files correctly so autogrowth rarely fires at all. Autogrowth should be a safety net, not a routine occurrence.

The Script: Generate ALTER DATABASE Statements for All Databases

The script below analyses every database file on your SQL Server instance and generates the appropriate ALTER DATABASE MODIFY FILE statement to set a sensible fixed-size growth increment. It handles both MB-based and percentage-based growth configurations.

The script does not execute the changes directly. It outputs a set of ALTER statements for you to review first. That's intentional. Always review generated DDL before running it against production systems.

DECLARE @ConfigAutoGrowth AS TABLE
(
    iDBID           INT,
    sDBName         SYSNAME,
    vFileName       VARCHAR(MAX),
    vGrowthOption   VARCHAR(12),
    vGrowth         BIGINT,
    vSize           BIGINT,
    cmd             VARCHAR(MAX)
)

-- Populate staging table with current file growth settings
INSERT INTO @ConfigAutoGrowth
SELECT
    SD.database_id,
    SD.name,
    SF.name,
    CASE is_percent_growth
        WHEN 1 THEN 'Percentage'
        WHEN 0 THEN 'MB'
    END AS GrowthOption,
    CASE WHEN is_percent_growth = 1 THEN growth ELSE growth * 8 / 1024 END,
    size * 8 / 1024,
    ''
FROM sys.master_files SF
INNER JOIN sys.databases SD
    ON SD.database_id = SF.database_id

-- Generate ALTER statements for files using fixed MB increments
-- Only flags files where the current increment doesn't already match the target
UPDATE @ConfigAutoGrowth
SET cmd =
    CASE
        WHEN vSize < 300    AND vGrowth <> 50  THEN 'ALTER DATABASE [' + sDBName + '] MODIFY FILE (NAME = ''' + vFileName + ''', FILEGROWTH = 50MB)'
        WHEN vSize BETWEEN 300  AND 1000 AND vGrowth <> 100 THEN 'ALTER DATABASE [' + sDBName + '] MODIFY FILE (NAME = ''' + vFileName + ''', FILEGROWTH = 100MB)'
        WHEN vSize BETWEEN 1000 AND 2000 AND vGrowth <> 200 THEN 'ALTER DATABASE [' + sDBName + '] MODIFY FILE (NAME = ''' + vFileName + ''', FILEGROWTH = 200MB)'
        WHEN vSize > 2000   AND vGrowth <> 400 THEN 'ALTER DATABASE [' + sDBName + '] MODIFY FILE (NAME = ''' + vFileName + ''', FILEGROWTH = 400MB)'
    END
WHERE vGrowthOption = 'MB'

-- Generate ALTER statements for files currently using percentage-based growth
-- All percentage-based files need remediation regardless of current value
UPDATE @ConfigAutoGrowth
SET cmd =
    CASE
        WHEN vSize < 300    THEN 'ALTER DATABASE [' + sDBName + '] MODIFY FILE (NAME = ''' + vFileName + ''', FILEGROWTH = 50MB)'
        WHEN vSize BETWEEN 300  AND 1000 THEN 'ALTER DATABASE [' + sDBName + '] MODIFY FILE (NAME = ''' + vFileName + ''', FILEGROWTH = 100MB)'
        WHEN vSize BETWEEN 1000 AND 2000 THEN 'ALTER DATABASE [' + sDBName + '] MODIFY FILE (NAME = ''' + vFileName + ''', FILEGROWTH = 200MB)'
        WHEN vSize > 2000   THEN 'ALTER DATABASE [' + sDBName + '] MODIFY FILE (NAME = ''' + vFileName + ''', FILEGROWTH = 400MB)'
    END
WHERE vGrowthOption = 'Percentage'

-- Output the statements that need to be applied
SELECT cmd
FROM @ConfigAutoGrowth
WHERE cmd IS NOT NULL

GO

How to Use This Script

  1. Connect to the SQL Server instance you want to audit in SSMS.
  2. Run the script in a query window. It will return a result set of ALTER DATABASE statements.
  3. Review the output carefully. Check that the target databases and file names look correct.
  4. If you want to exclude specific databases (for example, system databases or databases with unusual growth requirements), uncomment and modify the WHERE clause to filter them out.
  5. Once satisfied, copy the output statements and execute them. You can run them individually or paste the full result set and execute as a batch.

The script targets both data files and log files. That's correct behaviour. Log file growth increments are just as important as data file increments for managing VLF counts.

What to Watch Out For

A few practical notes before you run this across a production environment.

System databases. The script includes master, msdb, model, and tempdb by default. The system databases rarely grow significantly, but it's worth reviewing whether those changes are appropriate for your environment. You can exclude them by adding a filter on sDBName.

Databases already correctly configured. The first UPDATE block (for MB-based growth) only generates a statement if the current increment doesn't already match the target. Files already set to the correct value won't appear in the output. That's the expected behaviour.

This doesn't fix existing VLF fragmentation. Changing the auto-growth increment prevents future VLF fragmentation but doesn't repair existing fragmentation. If you have a transaction log with thousands of VLFs, you'll need to shrink and regrow the log file in controlled increments to resolve that separately.

Pre-sizing is better than relying on auto-growth. For databases with predictable growth patterns, the right long-term approach is to pre-size data and log files appropriately so auto-growth events are rare. This script gets your safety net settings into a sensible state, but it's not a substitute for proper capacity planning.

Key Takeaways

  • Default SQL Server auto-growth settings (1MB fixed or percentage-based) cause VLF fragmentation and autogrowth wait events in production environments.
  • Percentage-based auto-growth is particularly risky on large databases and should be converted to fixed MB increments.
  • Recommended fixed increments are 50MB, 100MB, 200MB, and 400MB, scaled to current database size.
  • The script above generates ALTER DATABASE statements for review rather than executing changes directly, giving you full control before any modification is made.
  • Fixing auto-growth settings is a safety net improvement. Pre-sizing database files to avoid frequent growth events is the correct long-term practice.

Misconfigured auto-growth settings are one of the items DBA Services checks during every SQL Server Health Check. If you're not confident your instance is configured correctly, or you want a full review of your SQL Server environment, contact DBA Services to discuss a health check or ongoing managed DBA support.