Sql-server – DBCC CHECKDB error

dbcc-checkdbsql serversql-server-2008

Occasionally, in my development environment, when I run CHECKDB, I run into this error:

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 0x0000c97c132000 in file
'S:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MYDB.mdf:MSSQL_DBCC8'.
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.

Why i am getting the operating system returned error 665?and How can I prevent this situation?

Best Answer

This article is a blog post from MSDN describing the issue. You should see if the symptoms match and take the actions listed in the article with caution.

To me it sounds like this from the blog:

Online DBCC has been susceptible to this limitation leveraging a secondary stream for copy-on-write activities. The sparse nature of DBCC snapshot or a snapshot database can drive attribute exhaustion.

As space is acquired the disk storage location(s) and size(s) are stored in the attribute structures. If the space is adjacent to a cluster already tracked by the file the attributes are compressed into a single entry, spanning the entire size. However, if the space is fragmented it has to be tracked with multiple attributes.

The 665 issue can pop up with larger file sizes. As the file grows it acquires more space. During the space acquisition the attributes are used to track this space.

Related Question