Will the Next Autogrowth Fit? Checking Growth Against Free Disk

Before the next autogrowth happens, you can check whether it will fit. Work out the size of the step from the file settings, then compare it with free space on the drive and with the file’s maximum. Files that share a drive share the same free space.

Why free space is not the whole story

Imagine a nightly load that stalls at 2 AM. The drive still shows free space, so everyone is confused. The file wanted to grow, and the step it needed was bigger than the room, or it would have crossed the file’s own maximum. Free space on the drive and the next growth step are two different numbers, and you need both.

The demo creates a database called SqlAuthorityDemo with a few awkward files, and drops it at the end. The first block creates it and gives the main data file a growth of 10 percent.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
GO
CREATE DATABASE SqlAuthorityDemo;
GO
ALTER DATABASE SqlAuthorityDemo
MODIFY FILE (NAME = SqlAuthorityDemo, FILEGROWTH = 10%);

Now add three more data files. One has a MAXSIZE just above its size. One has growth turned off. One has an absurd 12 TB growth step, which I added on purpose to trigger the warning. Do not copy that last one.

DECLARE @Folder nvarchar(300) = CONVERT(nvarchar(300), SERVERPROPERTY('InstanceDefaultDataPath'));
DECLARE @Sql nvarchar(max) = N'
ALTER DATABASE SqlAuthorityDemo ADD FILE
 (NAME = SqlAuthorityDemo_capped, FILENAME = N''' + @Folder + N'SqlAuthorityDemo_capped.ndf'',
  SIZE = 8MB, MAXSIZE = 10MB, FILEGROWTH = 4MB),
 (NAME = SqlAuthorityDemo_fixed, FILENAME = N''' + @Folder + N'SqlAuthorityDemo_fixed.ndf'',
  SIZE = 8MB, FILEGROWTH = 0),
 (NAME = SqlAuthorityDemo_huge, FILENAME = N''' + @Folder + N'SqlAuthorityDemo_huge.ndf'',
  SIZE = 8MB, FILEGROWTH = 12TB);';
EXEC (@Sql);

Read the units before you do any math

sys.master_files stores size, growth and max_size in 8 KB pages. When is_percent_growth is 1, growth is a percentage of the current size instead. A growth of 0 means automatic growth is off. A max_size of -1 means no limit.

SELECT name, type_desc, size, growth, is_percent_growth, max_size
FROM sys.master_files
WHERE database_id = DB_ID(N'SqlAuthorityDemo')
ORDER BY file_id;

Read the result row by row. The main file has growth 10 with is_percent_growth 1. The capped file grows by 512 pages, which is 4 MB, with a max_size of 1280 pages. The fixed file has growth 0. Mixing pages with percentages is how reports end up looking numeric and meaning nothing.

Calculate the next step

Turn each setting into bytes. For percent growth, take the percentage of the current size. I then round the step up to a 64 KB boundary. This is a planning estimate, not a promise about the exact bytes the engine will grow.

For the 8 MB main file, 10 percent is 838,860.8 bytes, and rounding lifts it to 851,968. The query also pulls the free bytes on the volume with sys.dm_os_volume_stats.

DROP TABLE IF EXISTS #GrowthCapacity;

WITH F AS
(SELECT database_id, file_id, name, type_desc, size, growth, is_percent_growth, max_size,
        CONVERT(decimal(38,2), size) * 8192 AS CurrentBytes,
        CASE WHEN is_percent_growth = 1
             THEN CONVERT(decimal(38,2), size) * 8192 * growth / 100.0
             ELSE CONVERT(decimal(38,2), growth) * 8192 END AS RawGrowthBytes
 FROM sys.master_files
 WHERE database_id = DB_ID(N'SqlAuthorityDemo') AND type IN (0, 1)),
G AS
(SELECT *, CASE WHEN growth = 0 THEN CONVERT(decimal(38,0), 0)
                ELSE CEILING(RawGrowthBytes / 65536.0) * 65536 END AS EstimatedGrowthBytes
 FROM F)
SELECT g.file_id, g.name, g.growth, g.max_size, g.CurrentBytes, g.RawGrowthBytes,
       g.EstimatedGrowthBytes, v.volume_mount_point, v.available_bytes
INTO #GrowthCapacity
FROM G AS g
CROSS APPLY sys.dm_os_volume_stats(g.database_id, g.file_id) AS v;

SELECT name, CurrentBytes, RawGrowthBytes, EstimatedGrowthBytes, available_bytes
FROM #GrowthCapacity
ORDER BY file_id;

Check each step against disk and MAXSIZE

Now compare. Is growth off? Does the step exceed free space? Does current size plus the step cross MAXSIZE? Keep the raw values in the output, so anyone can check your arithmetic.

SELECT name, CurrentBytes, EstimatedGrowthBytes, available_bytes, max_size,
       CASE WHEN growth = 0 OR max_size = 0 THEN 'Automatic growth unavailable'
            WHEN EstimatedGrowthBytes > available_bytes THEN 'Next step exceeds current free space'
            WHEN max_size > 0
             AND CurrentBytes + EstimatedGrowthBytes > CONVERT(decimal(38,0), max_size) * 8192
            THEN 'Next step crosses MAXSIZE, review remaining allowance'
            ELSE 'Step fits this snapshot, keep reserve' END AS GrowthCheck
FROM #GrowthCapacity
ORDER BY file_id;

The main file and the log fit. The capped file crosses MAXSIZE, because 8 MB plus 4 MB is more than 10 MB. The fixed file cannot grow at all. The huge file wants far more than the drive holds. Your free space will differ from mine, but those verdicts follow from the settings.

Do not add up free space

Several files can sit on one drive. Each row repeats the same free space, so summing available_bytes would count it many times. Group by the volume and use one free-space value. Add up only the growth steps.

SELECT volume_mount_point, COUNT(*) AS FilesOnVolume,
       SUM(EstimatedGrowthBytes) AS CombinedEstimatedSteps,
       MIN(available_bytes) AS SnapshotFreeBytes,
       CASE WHEN SUM(EstimatedGrowthBytes) > MIN(available_bytes)
            THEN 'Combined steps exceed snapshot free space'
            ELSE 'Combined scenario fits before reserve' END AS SharedVolumeCheck
FROM #GrowthCapacity
GROUP BY volume_mount_point
ORDER BY volume_mount_point;

All five files share one volume here, and the combined steps exceed its free space. That is a worst case, since not every file grows at once. It is still a useful way to see how little reserve you really have.

What this check cannot tell you

A file can have empty pages inside, so it may not need to grow soon. Check used space with FILEPROPERTY in the database that owns the file. Also, a bigger growth step means fewer growth events but a harder step to fit. Pick it from your workload, not from habit. Run the check again before a big load or index build, because free space changes by the minute.

DROP TABLE IF EXISTS #GrowthCapacity;
GO
USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

Run the check before the big job, not after the page.

Free disk is not growth capacity, it is shared headroom.

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