At the company I'm working for we use database snapshots in our ETL process to ensure the data is in a consistent state. The snapshot files are placed on a separate disk (in our case the X:).
One of the source databases is a 3TB database with data updated frequently. When creating a database snapshot of this database we've encountered moments when the snapshot files increased and we've got the error:
"Operating system error 112(There is not enough space on the disk.) encountered."
That error was always in the index maintenance windows, or when a bulk process ran in the source database. Since then we've increased the available disk space on the X:\ drive and changed the schedule of index maintenance/dbcc checkdb jobs. As expected, the errors didn't occur anymore.
From time to time we need an initial load of some large tables (600.000.000 rows / 300GB per table). This week we've encountered a different error in the load of these large tables:
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 0x000056fb976000 in file 'X:\Data\DatabaseName_snapshot.ss'.
Additional messages in the SQL Server error log and system event log may provide more detail.
This error occurs only 18 - 25 hours after the snapshot is created.
Things already tried
- Disabled all index maintenance/dbcc checkdb jobs and other processes changing a lot of data (except user input).
- Monitored the available space on the snapshot disk (X:\ 400GB). About 80% of the available space is free when the error occurs.
- Looked for simmilar questions on stackexchange. Found DB snapshot goes into SUSPECT mode (SQL 2014 on WIN2K8R2) which was not answered.
- Looking into article https://blogs.msdn.microsoft.com/psssql/2015/06/10/operating-system-error-665-file-system-limitation-not-just-for-dbcc-anymore/ right now.
Does anyone have more ideas to tackle this problem?
We are using SQL Server 2014 SP2 CU1, enterprise edition and Windows Server 2012.