Error 1101 and 665: Out of Disk Space and NTFS File System Limits

Fix it now

1101 means SQL Server needed a new page, the filegroup had none, and no file could grow to make one. 665 is a different problem wearing similar clothes: NTFS refused because a heavily fragmented file exhausted the attribute records it uses to track where the file lives. The first is about space and file settings, the second about fragmentation.

SELECT name, type_desc, size/128 AS size_mb, max_size, growth, is_percent_growth
FROM   sys.database_files;

SELECT DISTINCT vs.volume_mount_point, vs.available_bytes/1048576 AS free_mb
FROM   sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
WHERE  mf.database_id = DB_ID(N'MyDb');

1. If max_size is capped or growth is zero while the volume has room, correct it: ALTER DATABASE [MyDb] MODIFY FILE (NAME = N'MyDb', MAXSIZE = UNLIMITED, FILEGROWTH = 512MB);
2. If the volume is genuinely full, free space or add a file to the filegroup on a volume that has room.
3. For 665, stop looking at free space. Drop any stale database snapshots, and check whether the error appeared during a CHECKDB run.
4. For 665 during CHECKDB, run it with PHYSICAL_ONLY, or run it against a restored copy on another instance.
5. Re-run the operation that failed and watch whether autogrow now succeeds.

A file set to UNLIMITED is still limited to 16 TB. The current 1101 message says so in its own text, and on a very large database that ceiling is a real cause rather than a footnote.

If the allocation succeeds you are done. If not, the next section separates the two refusals properly.

Why it happens

Allocation happens inside a filegroup. When a page is needed and no free extent exists in any file of that filegroup, SQL Server tries to grow one of them. If growth is disabled, capped by a maximum size, refused by the operating system because the volume is full, or blocked by the 16 TB ceiling that applies even to a file marked UNLIMITED, there is nowhere to put the page and 1101 is raised. The current message names all of those causes and then lists the three remedies: drop objects in the filegroup, add files to it, or turn autogrowth on.

The neighbouring codes describe growth failing at different moments. 5149 is a MODIFY FILE hitting an operating system error while trying to expand a file. 1802 is CREATE DATABASE failing because some of the file names listed could not be created. 5144 records an autogrow that was cancelled or timed out, and 5145 an autogrow that completed but took long enough to be worth reporting. The last two are severity 10, so they sit in the error log looking harmless while telling you that the growth increment is too large or the storage is too slow.

665 is not a SQL Server error number at all. It is the Windows file system error “The requested operation could not be completed due to a file system limitation”, and Microsoft pairs it with error 1450, “Insufficient system resources exist to complete the requested service”. Its documented cause is specific: NTFS tracks where each part of a file lives in ATTRIBUTE_LIST_ENTRY records, adjacent space compresses into a single entry, and fragmented space does not. A heavily fragmented file therefore runs out of attribute records while the volume still shows plenty of free space.

That is why 665 so often appears during a nightly consistency check on a healthy-looking volume. DBCC CHECKDB uses a sparse file for its internal snapshot, and database snapshots use sparse files too. Sparse files fragment heavily as they fill, which is exactly the condition the limit responds to.

Full reference

Which one you are looking at

What Microsoft actually recommends for 665

There is no single quick fix, and it is worth knowing the real list before committing to a maintenance window. Microsoft’s documented options are these.

• Move to ReFS, which does not carry the same ATTRIBUTE_LIST_ENTRY limit. This is the durable answer.
• Defragment the NTFS volume with a transactional defragmentation utility, which requires shutting SQL Server down.
• Copy the database files to another volume, which lets them be packed more tightly and reduces attribute usage.
• Format NTFS with the /L option to obtain a large file record segment – with Microsoft’s own caveat that this might not be helpful when the problem is DBCC CHECKDB.
• Break a very large file into several smaller ones, for example one 8 TB file into eight of 1 TB, so fewer modifications land on each.
• Use larger autogrowth increments, so growth happens less often and in bigger pieces.
• Run DBCC CHECKDB WITH PHYSICAL_ONLY, which shortens the run and reduces the chance of hitting the limit.
• Stripe backup and bulk copy operations across several volumes rather than pointing them all at one.
• Run consistency checks during low activity, or on an availability group secondary or standby server instead.

Formatting with a 64 KB allocation unit is often recommended for this error in circulation. It is not in Microsoft’s list, and it is not what the documented cause responds to. The limit is on attribute records tracking fragments, not on cluster size.

Instant file initialization, and what it no longer excludes

That second half is a change worth registering, because the old rule – log files are always zeroed as they grow – stopped being true in SQL Server 2022. The default autogrowth increment for new databases is 64 MB, which is exactly the size that now benefits. Setting a much larger log growth increment gives that up.

Sizing so autogrow is a safety net rather than a routine

1. Pre-size data and log files to where you expect them in a year.
2. Use fixed increments in megabytes, never percentages.
3. Grant Perform volume maintenance tasks to the service account, unless the database uses TDE, in which case data file growth is zeroed regardless.
4. Leave autogrow enabled as a safety net. Turning it off means a busy Friday evening ends in errors rather than in a slightly larger file.
5. Alert on 5144 and 5145. They are severity 10 and will sit in the error log unnoticed otherwise.

Every code this article covers

Confirm the fix worked

1. Re-run the operation that failed and confirm it completes.
2. Confirm the file settings are what you intended: SELECT name, size/128 AS size_mb, max_size, growth, is_percent_growth FROM sys.database_files;
3. Confirm the volumes have headroom, using the sys.dm_os_volume_stats query above.
4. For 665, re-run DBCC CHECKDB (N'MyDb') WITH NO_INFOMSGS; and confirm it completes without the file system error.
5. Check the error log for new 5144 or 5145 entries over the following week, which would mean growth is still too slow.

Questions people ask about this