1

Our configuration is 2 SQL Servers 2016, enterprise edition, 13.0.5026.0, running on Windows Server 2012 R2.
Created availability group with one database in it. Database has 3 data files in it, all together around 2,5 TB in size We are creating 2xsnapshots on the database on secondary replica.

After some time our snapshots are turning into suspect state with following errors recorded in the error log:

Error: 3420, Severity: 21, State: 1.
G:\Snapshots01\Data\Data1.ss: Operating system error 665(The requested operation could not be completed due to a file system limitation) encountered.
Error: 17053, Severity: 16, State: 1.
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 0x0000a8c4c60000 in file 'G:\Snapshots01\Data\Data1.ss'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

I've checked snapshot file sizes with following query:

SELECT  DB_NAME(sd.source_database_id) AS [SourceDatabase],sd.name AS [Snapshot],
mf.name AS [Filename],size_on_disk_bytes/1024 AS [size_on_disk (KB)],mf2.size/128 AS [MaximumSize (MB)]
FROM sys.master_files mf
JOIN sys.databases sd ON mf.database_id = sd.database_id
JOIN sys.master_files mf2 ON sd.source_database_id = mf2.database_id AND mf.file_id = mf2.file_id
CROSS APPLY sys.dm_io_virtual_file_stats(sd.database_id, mf.file_id)
WHERE mf.is_sparse = 1AND mf2.is_sparse = 0

and it seems that snapshot is broken when the largest snapshot file reaches around 60GB.
Snapsot drive is 1,35 TB, formatted with NTFS 64KB.

What can we do to avoid errors like this?
Will changing to ReFS help or perhaps are there any other NTFS settings that can be ammended?

2

Operating system error 665(The requested operation could not be completed due to a file system limitation)

This is a common issue with large sparse files as it causes NTFS metadata fragmentation. You're not hitting a SQL Server issue, you're hitting (as the error states) a filesystem limitation (NTFS).

What can we do to avoid errors like this?

Not use snapshots. Not use NTFS (ReFS instead).

Will changing to ReFS help or perhaps are there any other NTFS settings that can be ammended?

Yes, changing to ReFS should alleviate the issue, assuming you actually need the snapshots. Changing less data in the source database would also help alleviate the issue.

No, there aren't really any other NTFS settings that can be changed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.