Setting Maximum Database File Sizes in SQL Server

This post shares how to set maximum database file sizes in SQL Server, capping your database file size limits for proper disk space management.

Configuring maximum database file sizes in SQL Server is often considered a safeguard or tactical measure rather than a primary strategy. Ideally, you want your databases to grow as needed, without arbitrary limits. However, there are situations where capping database sizes becomes a practical necessity.

Why Set Maximum Database File Sizes?

While it’s never ideal for a production database to stop updates or inserts due to size restrictions, there are valid reasons to enforce limits in certain scenarios:

1. Managing Disk Space in High-Usage Environments:
If unpredictable disk usage, such as massive temporary table operations in Temp DB, is causing space issues and triggering alerts, capping file sizes can prevent critical resources from being consumed entirely.

2. Handling Unpredictable User Behavior:
Ad hoc user queries that inadvertently consume large amounts of space can cause major disruptions. Educating users about resource usage takes time, and setting temporary limits may be necessary to maintain server stability in the interim.

3. Optimizing Multi-Tenant SQL Environments:
On SQL Server instances hosting thousands of small databases, capping file sizes helps ensure fair resource distribution and prevents a single database from monopolizing server capacity.

4. Custom Growth Management:
In some cases, you might cap database sizes intentionally and rely on custom jobs to manage file growth. For example, this can be useful in environments where disk expansion is carefully controlled or audited.

Capping database file sizes should generally be seen as a tactical measure, not a long-term solution. Addressing the root cause of unpredictable usage is the ultimate goal.

How to Set a Maximum Database File Size

Using SQL Server Management Studio (SSMS)

1. Right-click on the database you want to modify and select Properties.

2. Navigate to the Files tab in the left-hand menu.

3. In the database file settings, locate the Autogrowth/Maxsize column.

4. Click the button in this column to set the maximum file size (as shown below).
Note: Avoid setting Temp DB file sizes too small during testing. Use appropriate values based on your production environment.

Using T-SQL: ALTER DATABASE Statement

You can achieve the same result programmatically with the ALTER DATABASE statement. Here’s an example:

-- cap database max size to 100gb
ALTER DATABASE [YourDatabaseName]
MODIFY FILE (
    NAME = 'YourFileName',
    MAXSIZE = 100GB
);

This approach allows for precise control, and the size values can be specified in KB, MB, or GB.

Setting maximum file sizes is a useful short-term measure for stabilizing SQL Server, but it’s crucial to address root causes like query optimization, user education, or storage management to ensure long-term stability.

What Happens When the Cap Is Hit, Tested on SQL Server 2025

Before publishing this update I built a throwaway database on SQL Server 2025 with an 8MB data file capped at 8MB, then filled it. The moment the file cannot grow, every insert fails with error 1105, and this is the exact message your application will log:

Msg 1105: Could not allocate space for object 'dbo.filler' in database 'pw_maxsize_test'
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.

Two things are worth noticing in that message. It does not mention MAXSIZE anywhere, it says the filegroup is full, so a capped file and a genuinely full disk raise the same error and you have to work out which one you are dealing with. And the SQL Server 2025 wording confirms that even UNLIMITED data files carry a hard 16TB ceiling. I have a full write-up of this error, including how to tell the causes apart, on sqldba.blog in Could Not Allocate Space Because the Filegroup Is Full (Error 1105).

Some good news I also verified: raising the cap with ALTER DATABASE ... MODIFY FILE takes effect immediately and online. The insert that had just failed with 1105 succeeded straight after the MAXSIZE increase, with no restart and no shrink involved.

Check the Caps You Already Have

Before setting new limits, find out what is already capped. sys.database_files stores max_size in 8KB pages, with -1 meaning unlimited, so divide by 128 for MB:

-- Current size and max size per file, in MB
SELECT name, type_desc,
       size_mb     = size / 128,
       max_size_mb = CASE max_size
                       WHEN -1 THEN 'UNLIMITED'
                       ELSE CAST(max_size / 128 AS varchar(20)) + ' MB'
                     END
FROM sys.database_files;

Run that in each database you care about, and do not be surprised to find caps someone set years ago. An inherited MAXSIZE sits silent until the worst possible moment. For the instance-wide view I use the script on sqldba.blog at DBA Scripts: Get Database Sizes and Free Space, which covers every database in one pass.

Two Traps I Hit While Testing

You cannot set MAXSIZE below the current file size. If the file has already grown past where you want the cap, the ALTER fails with error 5040:

Msg 5040: MODIFY FILE failed for database 'pw_maxsize_test', file id 1.
Size of file (8192 KB) is greater than MAXSIZE (4096 KB).

So capping an already-large file means shrinking it first, and shrinking is its own can of worms. The order is shrink, then cap, and only shrink with a plan for the fragmentation it causes.

A cap does not stop growth events, it just decides where they end. A file with aggressive autogrowth and a cap will grow in normal-looking steps and then hit the wall with no warning. If you are capping files, you should be watching growth as well, and the query I use for that is on sqldba.blog in DBA Scripts: Get Autogrowth History. Seeing growth events accelerate towards a cap is your early warning, and it turns error 1105 from a 2am page into a planned change.