0

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?

Improve this question
0
1

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.

Improve this answer
6
  • thanks for your help .My Disk soace is 700GB free out of 16 TB .and my database size is 300GB .i did run DBCC CHECKDB while my backup job was running .do you think thats the problem or something else ?@Shaulinator – shamim Jan 19 '17 at 15:05
  • 1
    Are the backups on the same drive as the DB file? Have you watched your backup process? The drive we store our backups on sit around 40% free. When backups occur, the disk gets to about 90% full, but then the deletes commit and it goes back to 40% free. – Shaulinator Jan 19 '17 at 15:15
  • yes backups are on the same file as the Db file .i did not watch my backup process.best practice is to Run DBCC CHECKDB before backup job runs .whats your recommendation?@Shaulinator – shamim Jan 19 '17 at 15:23
  • I would run the DBCC CHECKDB and then run the backups like you mentioned and not run them simultaneously. I'd check the auto sizing of the DB's like the article mentions, then take it from there. Defragging the drive or reformatting it is more drastic and possibly a bit expensive as you have to move the data for reformatting and defragging disks with DB files is kinda sketchy to me. Some setups don't require defragging. community.spiceworks.com/topic/… – Shaulinator Jan 19 '17 at 15:33
  • thank you .your information helps .correcting my earlier comment "i just checked backups are not on the same drive .and my data file growth is disabled." additional comment would help@Shaulinator – shamim Jan 19 '17 at 15:52

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.