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?