SQL Server – Database Snapshot File System Limitation Error

snapshotsql server

Preface

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.

Problem

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

  1. Disabled all index maintenance/dbcc checkdb jobs and other processes changing a lot of data (except user input).
  2. Monitored the available space on the snapshot disk (X:\ 400GB). About 80% of the available space is free when the error occurs.
  3. Looked for simmilar questions on stackexchange. Found DB snapshot goes into SUSPECT mode (SQL 2014 on WIN2K8R2) which was not answered.
  4. 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.

Best Answer

The issue is around NTFS allocation maps and the amount of space they have for file allocations. In general, the more fragmented the file the more allocation mappings there needs to be.

By default when a volume is formatted for NTFS, small allocation map size is chosen (1024 bytes) whereas with /L the large allocation map size is chosen (4096 bytes).

Normally this doesn't come into play or show itself and any appreciable way... However once volumes get larger and larger or use technologies that create many small random allocations (such as database snapshots or checkdb) then it can rear its' ugly head.

To get around this, a few different options can be chosen:

  • Format the volume ntfs with /L:Enable
  • Make smaller volumes so that this doesn't happen
  • Use ReFS if on SQL 2014+

In most cases, choosing to format with /L:Enable will be the easiest and fastest option.

Related Question