Error 665 during CHECKDB

Question

When running a CHECKDB on our servers we ran into the following error:

The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x00021279006000 in file 'D:\MSSQL\Data\database1.mdf_MSSQL_DBCC34'.

Command being run:

DBCC CHECKDB (database) WITH ALL_ERRORMSGS, NO_INFOMSGS, PHYSICAL_ONLY

Setup:

• 5 server Availability Group in production environment

  • all physical servers with NVMe drives
  • SQL Server 2016
  • CHECKDB runs on all boxes
  • only failed on servers 3 and 5 (secondaries) in the AG
    • each server has over 700GB free on the D drive
  • Database is 2.2TB in size (1 file)

• 2 server Availability Group in test environment

  • all VMs with SAN drives
  • SQL Server 2019
  • CHECKDB runs on all boxes
  • only failed on server 1 (primary) in the AG
    • the server has over 300GB free on the D drive
  • Database is 2.6TB (2 files - 2.3TB and 280GB)

All this happened on the same night. Same database on production servers but a different database in test. I, of course, got called and started looking into it. I decided to run the CHECKDB again on server 3 in prod for the database that failed and it succeeded with no errors. We have since gotten the error again but it is random which made me wonder if it was activity based. According to our monitoring the activity is fairly normal.

Based on this article (https://www.mssqltips.com/sqlservertip/3008/solving-sql-server-database-physical-file-fragmentation/) we've looked at file fragmentation with CONTIG -A. The mdf files were not badly fragmented (2 and 4 fragments).

Based on this article (https://learn.microsoft.com/en-US/troubleshoot/sql/database-engine/database-file-operations/1450-and-665-errors-running-dbcc-checkdb) we've asked the server team to check disk fragmentation. Results:

Invoking analysis on SQL_DATA_1 (D:)...

The operation completed successfully.

Post Defragmentation Report:

    Volume Information:
            Volume size                 = 5.82 TB
            Free space                  = 769.03 GB
            Total fragmented space      = 0%
            Largest free space size     = 574.24 GB

    Note: File fragments larger than 64MB are not included in the fragmentation statistics.

    You do not need to defragment this volume.

Based on this article (https://www.sqlskills.com/blogs/paul/diskeeper-10-intelliwrite-corruption-bug/) we started looking at filter drivers but I not having too much luck identify any information in this vein.

We can now recreate the error in production at will. It requires us to do a full checkdb but it has errored each time since then.
DBCC CHECKDB (database) WITH ALL_ERRORMSGS

We are running the full check on another server in the production AG to see if fails with the same error. It does not fail with the physical only check.

Looking for advice on how to troubleshoot this further.

Answer 1

Looking for advice on how to troubleshoot this further.

There's really nothing to troubleshoot. CheckDB uses a sparse file, as such, depending on what is changed and where, the metadata for the file will have more or fewer allocations. You're at the point where, whatever is done, has finally choked up NTFS. It's really not worth looking into anything because you don't have control over any of it.

What can you do?

One option, if you want to stick with NTFS, is to make sure you're using a large record size. You can get this with the output of fsutil, fsutil fsinfo ntfsinfo drive:.

If you have a 1k (1024 byte) file record, then you can format the drive with the long filerecord size of 4k (4096) (/L option in cmd or -UseLargeFRS in powershell). This is destructive for the volume. This is no guarantee that it will not have more issues (in fact, since it's a filesystem limitation, it by default will have this issue at some other point). From Microsoft Learn, regarding the file record size:

Enables support for large file record segments (FRS). This is needed to increase the number of extents allowed per file on the volume. For large FRS records, the limit increases from about 1.5 million extents to about 6 million extents.

If you don't need to stay on NTFS, you can use ReFS, which does not have this issue.

Technically, there is a third option, which is to make all of your data files very small sizes. This won't stop the issue from possibly occurring, but it will minimize the chances. Having said that, I've witnessed this happen on file sizes as small as 60 GB.