Autogrowth Settings: Check Every Database File in SQL Server

To check autogrowth settings for every database file, read sys.master_files and turn its raw numbers into sizes you can judge. The view stores sizes in 8 KB pages, so the plain query is hard to read.

Why Autogrowth Settings Matter

A database file grows by itself when it runs out of room. Each growth has a step size, and the session that triggers it waits while SQL Server prepares the new space. A data file skips that preparation when instant file initialization is on. A log file waits for it. On SQL Server 2022 and later, steps of 64 MB or less skip it when instant file initialization is on.

A 1 MB step means many small pauses. A percent step starts small and gets bigger every time the file grows. Your autogrowth settings decide how long each pause lasts.

Three columns tell the story. They show the size now, the size of the next step, and the room left before the maximum. The script below builds a demo database with three files and three different problems. The database is named AutogrowthDemo, so run the script on a test server.

IF DB_ID(N'AutogrowthDemo') IS NULL CREATE DATABASE AutogrowthDemo;
GO
ALTER DATABASE AutogrowthDemo MODIFY FILE (NAME = N'AutogrowthDemo', SIZE = 400MB, FILEGROWTH = 10%);
ALTER DATABASE AutogrowthDemo MODIFY FILE (NAME = N'AutogrowthDemo_log', SIZE = 16MB, MAXSIZE = 100MB, FILEGROWTH = 1MB);
GO
DECLARE @folder nvarchar(260) = CONVERT(nvarchar(260), SERVERPROPERTY('InstanceDefaultDataPath'));
DECLARE @sql nvarchar(max) = N'ALTER DATABASE AutogrowthDemo ADD FILE (NAME = N''AutogrowthDemo_archive'', FILENAME = N''' + @folder + N'AutogrowthDemo_archive.ndf'', SIZE = 8MB, MAXSIZE = 8MB, FILEGROWTH = 0);';
EXEC (@sql);

Report the Growth Step and the Room Left

The report reads sys.master_files, which lists the files of every database on the instance. It divides the page counts by 128 to get megabytes. It turns the growth setting into text, and it adds the size of the next step. For a percent setting that step is the current size times the percentage. The last column shows how much room is left below the maximum, or No growth when the file can’t grow. Set @DatabaseName to NULL to list every database.

DECLARE @DatabaseName sysname = N'AutogrowthDemo';
SELECT d.name AS DatabaseName,
       mf.name AS LogicalName,
       mf.type_desc AS FileType,
       CONVERT(decimal(12,1), mf.size / 128.0) AS CurrentSizeMB,
       CASE WHEN mf.growth = 0 THEN N'None'
            WHEN mf.is_percent_growth = 1 THEN CONCAT(mf.growth, N' percent')
            ELSE CONCAT(CONVERT(decimal(12,1), mf.growth / 128.0), N' MB') END AS Autogrowth,
       CONVERT(decimal(12,1), CASE WHEN mf.growth = 0 THEN 0
            WHEN mf.is_percent_growth = 1 THEN mf.size * mf.growth / 100.0 / 128.0
            ELSE mf.growth / 128.0 END) AS NextStepMB,
       CASE WHEN mf.growth = 0 THEN N'No growth'
            WHEN mf.max_size = -1 THEN N'Unlimited'
            ELSE CONCAT(CONVERT(decimal(12,1), (mf.max_size - mf.size) / 128.0), N' MB') END AS RoomLeft
FROM sys.master_files AS mf
JOIN sys.databases AS d ON d.database_id = mf.database_id
WHERE @DatabaseName IS NULL OR d.name = @DatabaseName
ORDER BY d.name, mf.type, mf.file_id;

Read the three rows one at a time. The data file grows by 10 percent, so its next step is 40 MB. The step doubles when the file doubles, and at 100 GB it is 10 GB. The archive file can’t grow and is full at 8 MB. Any insert that needs a new page fails. The log file grows in 1 MB steps, so a busy log pauses its writers many times.

The Size Column Is the Current Size

A common mistake is to read the size column as the initial size. It is the current size. The demo shows it. The data file started at 8 MB, the first script resized it to 400 MB, and the report says 400. The view keeps no record of the original size. The exception is tempdb, where the column shows the size at startup. Read tempdb.sys.database_files for the current size.

When the Query Returns No Rows

An empty result is a permission problem, not a broken view. sys.master_files lists only the files that the caller is allowed to see. A new login with default rights sees no rows at all, while a sysadmin sees every file. Run the report as a login that has the rights, or use sys.database_files inside each database you can open. That view lists the files of the current database.

Fix the Weak Settings

Use a fixed step in megabytes, sized for your workload, and keep a maximum that leaves room. This script gives the data file a 256 MB step. The log file gets a 128 MB step and a 2 GB maximum. The archive file gets a little room. Run the report again afterwards.

ALTER DATABASE AutogrowthDemo MODIFY FILE (NAME = N'AutogrowthDemo', FILEGROWTH = 256MB);
ALTER DATABASE AutogrowthDemo MODIFY FILE (NAME = N'AutogrowthDemo_log', MAXSIZE = 2GB, FILEGROWTH = 128MB);
ALTER DATABASE AutogrowthDemo MODIFY FILE (NAME = N'AutogrowthDemo_archive', MAXSIZE = 64MB, FILEGROWTH = 8MB);

The steps are now fixed and predictable. The numbers are examples, not rules. A log that takes large loads needs a larger step than a log for a small application. Choose each step from the growth you see over a normal week.

Is Autogrowth a Plan?

You could argue that autogrowth saves you from thinking about file sizes. It’s a safety net, not a plan. Every growth is a pause for a user. Size the files for the next few months, and let autogrowth cover only the surprises. The report above shows how close each file is to its limits. I run it first when I check a server.

What to Remember

Check autogrowth settings with sys.master_files, and read three things: the current size, the next step and the room left. Prefer a fixed step to a percent step. Don’t leave a file with no growth unless you meant it. If the query returns nothing, look at permissions first.

Add mf.physical_name to the query when you need the file path. When you finish with the demo, run the cleanup script. It removes the database and all three files.

USE master;
GO
ALTER DATABASE AutogrowthDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE AutogrowthDemo;

A file size is not a setting, it is a history. The growth step is the part you can still change.

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