When attempting to run an online integrity check, or a backup on a large database (i.e. large data files), you may run into this error:

Error: 5123, Severity: 16, State: 1.
CREATE FILE encountered operating system error 665(failed to retrieve text for this error. Reason: 15105) while attempting to open or create the physical file 'D:\MSSQL\Data\db.mdf:MSSQL_DBCC9'.

Further investigation reveals that the error 665 is in fact: The requested operation could not be completed due to a file system limitation.

Cause

Backup and consistency (dbcc) check operations requires the creation of a database snapshot. These snapshots are created as sparse files, which only works on NTFS (not ReFS), but has some limitations.

Filesystem requirement

Check whether your volumes are all NTFS. Sparse files (and thus by extension: database snapshots) do not work with any other filesystem on Windows (such as ReFS). You'll need to migrate to NTFS first.

File extents limitations on NTFS

If this is not the case, then you most likely have run against the NTFS file extents limitation on your volume. There's no clear documentation on what this limit is, as it's technically not possible to find out, but compressed and sparse files are prone to reach this limit very quickly, especially with large files on fragmented volumes.

From Microsoft

An extent is a set of contiguous clusters on a volume. NTFS may allocate many extents to store a large file. All the extent information is saved in a file record in the master file table (MFT). If a file record is too small, NTFS allocates a secondary file record to store extent information. However, NTFS has a limited number of extents for a file. This limitation is defined by the maximum number of secondary file records of a file. The maximum extent number is reached when no additional secondary file record can be allocated to store extent information.

Read KB 967351 for more information about this limitation in NTFS.

Solution

The only viable permanent solution is to migrate to a volume which has large size file records enabled. When creating the volume, format it using the command line:

format V: /FS:NTFS /L

The parameter /L enables large size file records.

For this to work you need support for large size file records.

• For Windows 2008/Vista SP2 and 2008R2/7, install the hotfix 967351, which adds this feature.
• For Windows 2012/8, this feature is already present.

Workarounds

To temporarily work around the issue, try defragging the volume. For compressed and sparse files, you may have to resort to third party solutions such as Diskeeper.

For Windows 2008/Vista, install hotfix 957065 or Service Pack 1.

Also install the latest SP and updates for your SQL Server instance to resolve instability issues when this error condition occurs.

Workaround for integrity checks

In case of an integrity check, you can work around the issue by performing a check with locked tables:

DBCC CHECKDB db_name WITH TABLOCK

Note that this cannot be run on a live system as this will lock all tables until the check has completed.

Sources

• https://support.microsoft.com/en-us/kb/967351
• https://support.microsoft.com/en-us/kb/2002606
• https://support.microsoft.com/en-us/kb/957065