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
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.