8

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.

10

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

  • 5
    +1 especially for DBCC'ing against the backup, as long as it really is a backup and restore and not log ship/AG/mirror/repl. For some other tips see this post by me and this post by Paul Randal. The occasional DBCC during low volume periods against the production server is a good idea too, because it can still find physical issues on that hardware that would never show up on the dev server. – Aaron Bertrand Aug 18 '14 at 0:34
  • 2
    ...of course for that you can use WITH PHYSICAL_ONLY to avoid duplicating all the logical checks you're performing regularly against your dev server. – Aaron Bertrand Aug 18 '14 at 0:36
  • Defragmenting the hard drive won't cause the files to become less fragmented? – wayofthefuture May 26 '16 at 20:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.