Set a Maximum Database File Size in SQL Server (MAXSIZE)

🔧Part of the DBA-Tools Project, copy/paste SQL Server scripts and health checks.Related pillar: Storage & Capacity

A database file has filled a shared disk, or an insert failed with Msg 1105 saying a file reached “the maximum allowed size”. Either way the first check is the same: what max_size each file has today. Below is that query, the ALTER DATABASE to set or remove a cap, and the errors SQL Server raises when a file hits one.

A cap is a tactical move, not a strategy: most databases should grow when they need to, and you should hear about it when they do. It earns its place on a shared volume where one runaway process or ad hoc query must not take the disk, on a server hosting hundreds of small databases, or to hold a file in place while you find what is growing it. Every result here was run on SQL Server 2025.


Find the Caps You Already Have

Check before adding anything. sys.database_files stores max_size in 8 KB pages, with -1 meaning unlimited, so divide by 128 for MB:

-- current size and max size per file, in MB (run in the database)
SELECT name,
       type_desc,
       size_mb = size / 128,
       max_size_mb = CASE
                       WHEN max_size = -1 THEN 'UNLIMITED'
                       WHEN type_desc = N'LOG' AND max_size = 268435456 THEN 'UNLIMITED (2 TB log limit)'
                       ELSE CAST(max_size / 128 AS varchar(20)) + ' MB'
                     END
FROM sys.database_files;
sys.database_files query on MaxsizeDemo showing both files UNLIMITED, the log labelled as the 2 TB log limit

A log file created without a MAXSIZE does not store -1: it stores 268435456 pages, the 2 TB ceiling on a log file, so the query labels that as unlimited rather than showing it as a cap. Any other number is a real cap, and an inherited one sits silent until the file reaches it. For every database at once, use Get Database Sizes and Free Space.


Set MAXSIZE With T-SQL

-- cap the data file at 100 GB
ALTER DATABASE [SalesDB]
MODIFY FILE (NAME = N'SalesDB', MAXSIZE = 100GB);

-- cap the log at 20 GB
ALTER DATABASE [SalesDB]
MODIFY FILE (NAME = N'SalesDB_log', MAXSIZE = 20GB);

-- remove a cap
ALTER DATABASE [SalesDB]
MODIFY FILE (NAME = N'SalesDB', MAXSIZE = UNLIMITED);

NAME is the logical file name from the query above, not the path on disk. Sizes take KB, MB, GB or TB. Setting UNLIMITED on the data file puts max_size back to -1.


Set It in SSMS

Right-click the database, Properties, then the Files page. The … button in the Autogrowth / Maxsize column opens the growth dialog for that file, where the maximum file size can be limited to a value in MB. The Script button at the top of Database Properties gives you the same ALTER DATABASE, which is the version worth keeping.


What Happens When a Data File Hits the Cap

Tested with an 8 MB data file capped at 10 MB and filled with 8 KB rows. Once the file could not grow, the insert failed with:

Msg 1105  ·  Level 17  ·  State 2
Could not allocate space for object 'dbo.Filler' in database 'YourDb' because the 'PRIMARY' filegroup is full due to lack of storage space or database files reaching the maximum allowed size. Note that UNLIMITED files are still limited to 16TB. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Msg 1105 in SSMS: the PRIMARY filegroup is full once the data file reaches its 10 MB MAXSIZE

The message never says MAXSIZE. A cap, autogrowth switched off and a full disk all raise the same 1105, and the way to tell them apart is in Could Not Allocate Space Because the Filegroup Is Full (Error 1105). Level 17 also ends the batch, so a loop or a multi-statement job step stops at the first failed insert.

Raising the cap is immediate and online. In the same test, MODIFY FILE (MAXSIZE = 50MB) went straight through and the next insert succeeded with no restart and nothing else changed.

MAXSIZE raised to 50 MB online, the next insert succeeds and rows_now returns 802

A Capped Log Raises 9002 Instead

Cap the log and fill it inside one open transaction, and the error is the log full message, not 1105:

Msg 9002  ·  Level 17  ·  State 4
The transaction log for database 'YourDb' is full due to 'ACTIVE_TRANSACTION' and the holdup lsn is (46:24:1).
Msg 9002 in SSMS: the capped transaction log is full due to ACTIVE_TRANSACTION

The reason in the quotes changes with whatever is holding the log, so read it rather than assuming. SQL Server Disk Is Full or the Log Will Not Truncate covers each one.


You Cannot Cap a File Below Its Current Size

Msg 5040  ·  Level 16  ·  State 1
MODIFY FILE failed for database 'YourDb', file id 1. Size of file (8192 KB) is greater than MAXSIZE (4096 KB).
Msg 5040 in SSMS: MODIFY FILE fails because MAXSIZE 4 MB is below the current 8 MB file size

Capping a file smaller than it is today means shrinking it first, and shrinking brings its own problems. The order is shrink, then cap: How to Shrink SQL Server Database Files in Chunks.


A Cap Does Not Warn You

A file with normal autogrowth and a cap grows in ordinary-looking steps and then stops dead. If you cap files, watch the growth as well: Get Autogrowth History shows growth events speeding up, and that is the early warning. Alerting on error 1105 is the last line, not the first.


Frequently Asked Questions

Is MAXSIZE the same as the Express edition size limit?
No. Express has a fixed per-database limit set by the edition, which you cannot raise. MAXSIZE is a limit you choose per file, on any edition, and you can change it at any time.
Should I cap tempdb?
Only as a stop-gap. A capped tempdb turns a disk alert into failing queries. Find the session that is filling it: TempDB Is Full or Still Growing.
Does raising MAXSIZE need downtime?
No. It is an online metadata change, tested here with the insert that had just failed succeeding straight after the new cap.

Related

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *