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.
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.
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.
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.
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.
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.
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.