Why SQL Server's Default File Growth Settings Will Hurt You

SQL Server's default autogrowth increment is 1MB for data files and 10% for log files on older installations, and while Microsoft adjusted some defaults in later versions, many production environments are still running with settings that are far too small for the workload they're carrying. The fix is straightforward: update the file growth settings across all databases using a bulk T-SQL script rather than clicking through each database manually in SSMS.

Left uncorrected, undersized autogrowth increments cause real problems. Every time SQL Server hits the end of an allocated file, it has to pause and extend the file before the write can complete. On a busy system, this can happen dozens of times per hour. Each growth event introduces latency, fragments the file on disk, and shows up in your wait statistics as WRITELOG or IO-related waits. It's entirely avoidable overhead.

What Are the Right File Growth Settings?

There's no single correct answer, but there are well-established guidelines. Microsoft and the SQL Server community broadly recommend fixed-size increments rather than percentage-based growth, because percentage growth produces increasingly large and unpredictable growth events as the database grows.

A practical starting point for most production environments:

  • Data files (.mdf / .ndf): 512MB to 1024MB fixed increments for databases over 50GB. For smaller databases, 256MB is reasonable.
  • Log files (.ldf): 256MB to 512MB fixed increments. Log file autogrowth is particularly painful because it can stall transactions while the file extends.
  • Avoid percentage-based growth entirely on production databases. A 10% growth event on a 500GB database means a 50GB file extension, which is not something you want happening mid-business-day.

The right value for your environment depends on your database size, growth rate, and available disk space. The scripts below give you the visibility and control to make that call correctly.

Step 1: Check Your Current File Growth Settings

Before changing anything, audit what you're working with. This query pulls the current autogrowth configuration for every database on the instance from sys.master_files:

SELECT
    DB_NAME(mf.database_id)                             AS database_name,
    mf.name                                             AS logical_name,
    CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL, size)/128)) AS file_size_MB,
    CASE mf.is_percent_growth
        WHEN 1 THEN 'Yes'
        ELSE 'No'
    END                                                 AS is_percent_growth,
    CASE mf.is_percent_growth
        WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
        WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
    END                                                 AS growth_increment,
    CASE mf.is_percent_growth
        WHEN 1 THEN CONVERT(DECIMAL(20,2), (((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
        WHEN 0 THEN CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL, growth)/128))
    END                                                 AS next_autogrowth_size_MB,
    CASE mf.max_size
        WHEN 0  THEN 'No growth allowed'
        WHEN -1 THEN 'File will grow until disk is full'
        ELSE CONVERT(VARCHAR, mf.max_size)
    END                                                 AS max_size,
    mf.physical_name
FROM sys.master_files mf
ORDER BY database_name, logical_name;

Run this first and review the output. Pay particular attention to the is_percent_growth column and the next_autogrowth_size_MB column. If you're seeing percentage-based growth, or fixed increments of 1MB or 64MB on large databases, you need to act.

Step 2: Update File Growth Settings Across All Databases

The script below updates the autogrowth settings for all user databases on the instance in one pass. It uses a staging table and a cursor to iterate through each database and file, then applies an ALTER DATABASE ... MODIFY FILE command dynamically.

Before running this, find the line marked with the comment and set your target growth increment. The example below uses 128MB - adjust this to suit your environment.

-- Create staging table
IF EXISTS (SELECT name FROM sys.sysobjects WHERE name = N'ConfigAutoGrowth' AND xtype = 'U')
    DROP TABLE ConfigAutoGrowth
GO

CREATE TABLE DBO.ConfigAutoGrowth
(
    iDBID        INT,
    sDBName      SYSNAME,
    vFileName    VARCHAR(MAX),
    vGrowthOption VARCHAR(12)
)
PRINT 'Table ConfigAutoGrowth created.'
GO

-- Populate staging table with current file info
INSERT INTO DBO.ConfigAutoGrowth
SELECT
    SD.database_id,
    SD.name,
    SF.name,
    CASE SF.status
        WHEN 1048576 THEN 'Percentage'
        WHEN 0       THEN 'MB'
    END AS GrowthOption
FROM SYS.SYSALTFILES SF
JOIN SYS.DATABASES SD ON SD.database_id = SF.dbid
GO

-- Apply new autogrowth settings across all user databases
DECLARE @name          VARCHAR(MAX)
DECLARE @dbid          INT
DECLARE @vFileName     VARCHAR(MAX)
DECLARE @vGrowthOption VARCHAR(MAX)
DECLARE @Query         VARCHAR(MAX)

DECLARE db_cursor CURSOR FOR
    SELECT iDBID, sDBName, vFileName, vGrowthOption
    FROM ConfigAutoGrowth
    WHERE sDBName NOT IN ('master', 'msdb', 'model', 'tempdb') -- Add any other DBs to exclude here

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbid, @name, @vFileName, @vGrowthOption

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Updating autogrowth for database: ' + UPPER(@name)

    -- *** Update the FILEGROWTH value below to match your target increment ***
    SET @Query = 'ALTER DATABASE ' + @name + '
        MODIFY FILE (NAME = ' + @vFileName + ', FILEGROWTH = 128MB)' -- <<-- SET YOUR INCREMENT HERE

    EXECUTE(@Query)

    FETCH NEXT FROM db_cursor INTO @dbid, @name, @vFileName, @vGrowthOption
END

CLOSE db_cursor
DEALLOCATE db_cursor
GO

Step 3: Verify the Changes Applied

Once the script completes, re-run the audit query from Step 1 to confirm all files have been updated. Alternatively, use this quick verification query:

SELECT
    DB_NAME(database_id)    AS database_name,
    name                    AS logical_name,
    type_desc,
    CASE is_percent_growth
        WHEN 1 THEN CONVERT(VARCHAR(5), growth) + '%'
        ELSE CONVERT(VARCHAR(20), (growth/128)) + ' MB'
    END                     AS autogrowth_value,
    CASE max_size
        WHEN -1 THEN 'Unrestricted'
        WHEN  0 THEN 'Growth disabled'
        ELSE CONVERT(VARCHAR(20), max_size/128) + ' MB'
    END                     AS max_size
FROM sys.master_files
WHERE database_id > 4 -- Excludes system databases
ORDER BY database_name, type_desc;

All files should now show your chosen fixed increment in the autogrowth_value column, with no percentage-based entries remaining.

What About New Databases?

The scripts above fix existing databases, but any new database created on the instance will inherit its initial file settings from the model database. Update model as well to ensure new databases start with sensible defaults:

-- Update model database so new databases inherit correct autogrowth settings
ALTER DATABASE model MODIFY FILE (NAME = 'modeldev', FILEGROWTH = 256MB);
ALTER DATABASE model MODIFY FILE (NAME = 'modellog', FILEGROWTH = 64MB);

Adjust the logical file names if they differ in your environment - check sys.master_files where database_id = 3 to confirm.

What to Watch Out For

A few things worth knowing before you run this in production:

  • Test in non-production first. The dynamic SQL in the cursor script is straightforward, but always validate the output on a dev or staging instance before touching production.
  • Instant File Initialisation matters. Data file growth events are much faster when Instant File Initialisation (IFI) is enabled for the SQL Server service account. Log files cannot use IFI regardless. Check whether IFI is enabled on your instance - it's a common gap in SQL Server configurations.
  • This doesn't fix fragmentation already caused by previous growth events. If your data files have been growing in 1MB chunks for years, the underlying VLF structure of your log files and the physical file layout may already be fragmented. That's a separate remediation task.
  • Excluding databases: The script excludes master, msdb, model, and tempdb by default. Add any other databases to that exclusion list as needed. Tempdb autogrowth is a separate configuration topic best handled at instance setup.
  • Disk space: Larger growth increments mean each autogrowth event consumes more space in one hit. Make sure your data volumes have sufficient headroom before increasing increment sizes significantly.

Key Takeaways

  • SQL Server's default autogrowth settings (1MB for data files, 10% for log files on older versions) are too small for most production workloads and cause unnecessary IO overhead and transaction latency.
  • Fixed-size growth increments are always preferable to percentage-based growth on production databases. Use 256MB to 1024MB for data files and 64MB to 512MB for log files depending on database size.
  • The audit query against sys.master_files gives you a complete picture of current growth settings across all databases before you make any changes.
  • Update the model database after running the bulk script, so any new databases created on the instance inherit the correct settings automatically.
  • Instant File Initialisation should be enabled on every SQL Server instance to reduce the impact of data file growth events.

Reviewing file growth settings is one of the checks included in a DBA Services SQL Server Health Check. If you're not sure whether your instance is configured correctly across the board, our team can assess your environment and provide prioritised recommendations. Get in touch to find out more.