What’s the issue?
Every SQL Server database file has a maximum size setting that controls how large the file can grow. By default, this is set to UNLIMITED, allowing the file to grow until it fills the underlying volume. When a maximum size is configured, SQL Server stops growing the file once it reaches that limit and returns errors on operations that need additional space.This finding indicates that one or more tempdb data or log files have a maximum size value set rather than UNLIMITED. The setting is sometimes applied as a safety measure to prevent tempdb from filling a drive, but it introduces operational risk on a database that is critical to the entire instance.
Why is this a problem?
tempdb is shared by every database and every session on the SQL Server instance and is used for sorts, hash joins, spills, temporary tables, table variables, snapshot version store, and many internal operations. When tempdb runs out of space, queries fail with error 1105, sessions are disrupted, and in severe cases the entire instance can become unstable until space is freed.Setting a maximum size on tempdb files creates an artificial ceiling that triggers these failures even when free space is still available on the drive. A single large query, an unexpected sort spill, or a long running transaction holding the version store open can hit the cap and cause widespread query failures across all databases on the instance.
The intended safety benefit (preventing tempdb from filling the drive) is better achieved through proper drive sizing and monitoring than through a hard size limit. A capped tempdb shifts the failure from a drive level event to a query level event, but the failure still occurs and is often more disruptive because it surfaces as application errors during normal operation.
What should you do about this?
Identify tempdb files with a maximum size set by querying sys.master_files where database_id = 2 and reviewing the max_size column (a value other than 0 or 268435456 indicates a non default cap). For each file, change the maximum size to UNLIMITED using ALTER DATABASE tempdb MODIFY FILE (NAME = N’LogicalFileName’, MAXSIZE = UNLIMITED);. The change takes effect immediately and does not require a service restart.Place tempdb on a dedicated drive sized to accommodate the largest expected workload, with headroom for growth spikes. Pre size the tempdb data and log files to fill most of the drive at startup so the files do not need to autogrow during normal operation, which also helps performance by avoiding fragmentation and growth pauses.
Configure monitoring to alert on tempdb drive space and tempdb file usage so you have early warning of unusual activity before it becomes a problem. Review queries that spill heavily to tempdb and tune them where practical, since reducing tempdb pressure is more sustainable than capping its size.