Capping File Growth So One Query Cannot Fill a Whole Disk

Capping file growth means setting a MAXSIZE on a database file. One runaway workload then fails inside its own database, not across the whole disk. The cap does not add space. It decides where the failure happens.

The 2 AM disk alert

Picture the page. The data drive is at zero percent free, and thirty databases share it. One loader went wild, and every database on that drive is now throwing errors.

Nobody set a ceiling, so the file just kept growing. SQL Server did what it was told. A cap on that one database would have stopped the loader at its own boundary. The other twenty-nine would never have noticed.

Let me show it on a small demo database called SqlAuthorityDemo. The demo creates it here and drops it at the end. First, look at the default ceilings.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
GO
CREATE DATABASE SqlAuthorityDemo;
GO
USE SqlAuthorityDemo;
GO
SELECT name, type_desc, size * 8 / 1024 AS size_mb,
       max_size, growth * 8 / 1024 AS growth_mb
FROM sys.database_files
ORDER BY file_id;

Read the default ceilings

The column max_size counts 8 KB pages. A value of -1 means the file may grow until the disk is full. That is the data file here. The log file has a limit, but it is huge: 268435456 pages, which is 2 TB. Both files grow 64 MB at a time.

To check your own server, run the same query against sys.master_files instead of sys.database_files. It lists every file on the instance. Any data file showing -1 on a shared drive deserves a conversation.

Set the cap

Now give each file a ceiling and a sensible growth step. MAXSIZE limits growth. It does not shrink a file or free any space, so pick a number above what the file uses today.

I also turn off Query Store for this demo. It writes into the same data file, and I want the only writer to be my loop.

ALTER DATABASE SqlAuthorityDemo SET QUERY_STORE = OFF;
ALTER DATABASE SqlAuthorityDemo
    MODIFY FILE (NAME = SqlAuthorityDemo, FILEGROWTH = 8MB);
ALTER DATABASE SqlAuthorityDemo
    MODIFY FILE (NAME = SqlAuthorityDemo, MAXSIZE = 32MB);
ALTER DATABASE SqlAuthorityDemo
    MODIFY FILE (NAME = SqlAuthorityDemo_log, FILEGROWTH = 8MB);
ALTER DATABASE SqlAuthorityDemo
    MODIFY FILE (NAME = SqlAuthorityDemo_log, MAXSIZE = 64MB);
GO
SELECT name, type_desc, size * 8 / 1024 AS size_mb,
       max_size * 8 / 1024 AS max_mb, growth * 8 / 1024 AS growth_mb
FROM sys.database_files
ORDER BY file_id;

The data file now stops at 32 MB and the log at 64 MB. Both grow in 8 MB steps.

Hit the cap on purpose

Next, a loop plays the runaway loader. Each row is about 8 KB, and 6000 rows would need about 48 MB. The cap is 32 MB, so something has to give. The loop stops in the CATCH block and prints the error.

SET NOCOUNT ON;
CREATE TABLE dbo.CapProbe (Payload char(8000) NOT NULL);

DECLARE @Attempt int = 0;
BEGIN TRY
    WHILE @Attempt < 6000
    BEGIN
        INSERT dbo.CapProbe VALUES (REPLICATE('x', 8000));
        SET @Attempt += 1;
    END;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS error_number, ERROR_MESSAGE() AS error_message;
END CATCH;

SELECT COUNT(*) AS rows_committed FROM dbo.CapProbe;

The error is 1105, and the message says the PRIMARY filegroup is full. Every insert before that point stayed committed. Your row count may differ from mine. The point is that the failure is local. Only this database stopped.

Tell data full from log full

A full data file and a full log look alike on a pager. They are not. Error 1105 is data. Error 9002 is the log. This query tells you which one is at its limit.

SELECT name, log_reuse_wait_desc
FROM sys.databases
WHERE name = N'SqlAuthorityDemo';

SELECT name, size * 8 / 1024 AS size_mb,
       FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024 AS used_mb
FROM sys.database_files
ORDER BY file_id;

The data file shows used_mb equal to size_mb, so it is full. The log file still has room and is nowhere near its own cap. The data file cap was the one that fired.

Add an alert before the ceiling

A cap turns a disk outage into a database outage. That is better, but it is still an outage. So pair it with an alert that gives someone time to act.

This query shows room inside each file, room left to grow under the cap, and free space on the volume. Schedule it and warn at a threshold that leaves a few hours, not a few minutes.

SELECT f.name,
       f.size * 8 / 1024 AS size_mb,
       (f.size - FILEPROPERTY(f.name, 'SpaceUsed')) * 8 / 1024 AS free_inside_mb,
       (f.max_size - f.size) * 8 / 1024 AS growth_left_mb,
       v.available_bytes / 1048576 AS volume_free_mb
FROM sys.database_files AS f
CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.file_id) AS v
ORDER BY f.file_id;

The volume number depends on your drive, so I do not quote it. In this run the data file shows 0 free inside and 0 growth left, because it sits at its cap. When you get the alert, decide what happens next: stop the loader, end the bad transaction, approve more space, or trim old data.

When you finish, drop the demo database.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

Set the ceiling before the 2 AM page, and leave yourself enough warning to use it.

A file cap is not extra capacity, it is a boundary for one database.

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