Have a SQL Server 2014 instance running on Windows 2012 R2 that weekly runs Ola Hallengren's DB integrity scripts and the last two weeks started getting this error:
DESCRIPTION: 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 0x000036ec240000 in file 'E:\DBFiles\XXXXX.mdf_MSSQL_DBCC42'. 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 checked both the event log and the SQL Server error log but not getting a lot of additional info that could help. I'm rerunning the DBCC check for this db as I did last week and it cleared out the MSSQL_DBCC42 file and didn't find any corruption.
Any other places I could find more info other than event log and SQL Server error log? Should I ask our storage group to see if something shows up on reports for the SAN or is this purely SQL related due to some contention?
This database is pretty active as its replicating the transactions from our JDE financials and manufacturing environment on an iSeries, and currently is about 300gb. Each night the database is backed up and restored to a development server. Would it be better for me to run DBCC check against that copy of the database? This DB is a source for building SSAS multi-dimensional cubes for previous day reporting so I would have the option of a complete rebuild of the data or trying to recover from a backup if it became corrupt etc.
Appreciate any thoughts on what could cause this error or the best option for running DBCC checkdb based on the situation.
Best Answer
DBCC uses snapshots internally. Snapshots are then implemented as sparse files in Windows.
So this is actually a problem with Windows' handling of sparse files, which causes the snapshots used by DBCC to occasionally break on large and very active databases. It's reasonably well documented with a few recommended fixes in http://support.microsoft.com/kb/2002606
However, if you can DBCC on the dev server I'd recommend that instead. (I've personally only seen this problem with databases that were still serving traffic while being DBCC'd/snapshotted. Backup/restore to an idle server, DBCC there => no more 665 errors.)