A data or log file cannot grow any further

What this check looks for

Every online, writeable, non-snapshot user database is examined, tempdb excluded because it is rebuilt on every restart. Two conditions, reported separately:

Autogrowth is off and the file is over 80 percent used. growth = 0, and used space divided by file size is above 0.80. There is no headroom and no mechanism to create any.

Autogrowth is on but the file is over 90 percent of its maximum size. growth > 0, max_size > 0, and current size divided by maximum size is above 0.90. The file can still grow, but not much, and then it stops.

A log file with no real maximum carries max_size of 268435456 pages, which is a sentinel rather than a limit, and is excluded.

Why it matters

There is no warning beforehand, no autogrow event to find afterwards, and no gradual slowdown. There is an error and an application that is down.

• Error 1105 when a data file fills: “Could not allocate space for object … in database … because the filegroup is full.”
• Error 9002 when a log file fills: “The transaction log for database … is full.”

Both arrive at full speed from a database that was working perfectly a second earlier.

Free space on the drive does not help. This is the part that misleads every investigation. A file that has reached its configured maximum size fails on a drive with a terabyte free, and the first response is always to check the drive, find plenty of room, and conclude the error must mean something else.

The two settings get this way for understandable reasons:

• Autogrowth turned off by somebody who read that autogrowth hurts performance. It does, in the sense that a growth event pauses writes while the file is extended. The answer is to size the file properly and set a sensible growth increment, not to remove the safety net.
• A maximum size that was a reasonable number on a smaller server several years ago, set by a person who is no longer here, on a database that has since grown ten times.

How to confirm it yourself

Run this in each database, or against the one named in the finding:

SELECT DB_NAME()                                            AS [database_name],
       f.[name]                                             AS [logical_name],
       f.[type_desc],
       CAST(f.[size]     * 8.0 / 1024 AS DECIMAL(12,1))     AS [size_mb],
       CAST(FILEPROPERTY(f.[name], 'SpaceUsed') * 8.0 / 1024 AS DECIMAL(12,1)) AS [used_mb],
       CAST(FILEPROPERTY(f.[name], 'SpaceUsed') * 100.0 / NULLIF(f.[size], 0) AS DECIMAL(5,1)) AS [pct_used],
       CASE WHEN f.[growth] = 0 THEN 'OFF'
            WHEN f.[is_percent_growth] = 1 THEN CAST(f.[growth] AS VARCHAR(10)) + ' %'
            ELSE CAST(f.[growth] * 8 / 1024 AS VARCHAR(10)) + ' MB' END AS [autogrowth],
       CASE WHEN f.[max_size] = -1 THEN 'unlimited'
            WHEN f.[max_size] = 268435456 THEN 'unlimited (log)'
            ELSE CAST(f.[max_size] * 8 / 1024 AS VARCHAR(20)) + ' MB' END AS [max_size],
       f.[physical_name]
  FROM sys.database_files AS f WITH (NOLOCK)
 WHERE f.[type] IN (0, 1)
 ORDER BY [pct_used] DESC;

How to fix it

Two decisions, in this order.

First, remove the immediate ceiling. This is safe and takes seconds:

-- give the file room to grow again
ALTER DATABASE [YourDatabase]
  MODIFY FILE ( NAME = N'YourLogicalFileName', MAXSIZE = UNLIMITED );

-- and turn autogrowth on, in fixed megabytes rather than a percentage
ALTER DATABASE [YourDatabase]
  MODIFY FILE ( NAME = N'YourLogicalFileName', FILEGROWTH = 512MB );

Use a fixed size in megabytes, not a percentage. Percentage growth compounds: a 10 percent growth on a 100 GB file is a 10 GB growth event, and the database stops while it happens. There is a separate check for percentage growth for this reason.

Second, grow the file deliberately rather than leaving it to autogrow under load:

ALTER DATABASE [YourDatabase]
  MODIFY FILE ( NAME = N'YourLogicalFileName', SIZE = 200GB );

Autogrowth is the safety net. Sizing the file for what it needs is the plan. A file that autogrows every day is a file that was sized wrongly.

Make sure instant file initialization is on before growing a large data file. Without it, SQL Server zeroes every byte of the new space and the growth takes minutes rather than seconds. It does not apply to log files, which are always zeroed.

Then check the drive. Removing the maximum size moves the ceiling to the volume, so confirm there is genuinely room there. That is the next check along.

How long it takes

About two hours, mostly the file growth itself and confirming the drive can take it. The setting changes are immediate.

Related reports

Related checks

Frequently asked questions

The drive has plenty of space. Why is this a problem? Because the limit is on the file, not the drive. A file at its MAXSIZE fails no matter how empty the volume is. That is the specific trap this check exists to catch.

Is autogrowth off ever correct? On a file you size deliberately and monitor closely, it is defensible. It is a considered choice for a specific database, not a default to apply everywhere, and it requires that somebody is actually watching.

80 percent for one condition and 90 for the other. Why different? A file with no autogrowth has no mechanism to save itself, so the warning comes earlier. A file that can still grow has a little more time.

Why is tempdb excluded? It is recreated at every restart from the model settings, so its current size says nothing about a persistent problem. There are separate tempdb checks.