DBCC checkdb failing

MD SHAMIM
MD SHAMIM used Ask the Experts™
on
Hi all ,

recently my DBCC checkdb job is failing in production ,after spending some time in google search did not find the answer i was looking for .DBCC chekcdb job failing with error below

DBCC CHECKDB (MyDB) WITH all_errormsgs executed by -sqlsvc found 0 errors and repaired 0 errors. Elapsed time: 6 hours 48 minutes 0 seconds. Internal database snapshot has split point LSN = 00fcd2af:00006980:0012 and first LSN = 00fcd2af:00006911:0001.

S:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\myDB_F2.ndf:MSSQL_DBCC7: Operating system error 665(The requested operation could not be completed due to a file system limitation) encountered.

Error: 17053, Severity: 16, State: 1.

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 0x00005d4c76a000 in file 'S:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\myDB_F2.ndf:MSSQL_DBCC7'. 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.

what i did :

1. i make sure there is no jobs are running in same time DBCC checkdb run

2.my database is 600GB and i know DBCC checkdb create internal snapshot so i make sure i have lots of space ,space is not an issue .i have 2TB free space .


please help .

thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
" Additional messages in the SQL Server error log and system event log may provide more detail. " - did you checked SQL logs and event viewer?
Is S:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\myDB_F2.ndf on SAN or some local RAID?

Regardles - you could try run the DBCC like below in order to minimize the performance impact of DBCC CHECKDB

DBCC CHECKDB (DBnameHere) WITH PHYSICAL_ONLY;

Author

Commented:
i did check SQL logs but no luck to finding answers .

Is S:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\myDB_F2.ndf on SAN or some local RAID?

ans: its on DAS ,locally attached .


@Icohan
lcohanDatabase Analyst

Commented:
DAS ,locally attached

As the answer is very general -  can it be more specific? like connection type? iSCSI, fiber, etc...? RAID level? etc..

I'm asking because I believe the main reason why DBCC fails it may be hidden in some of those specific logs like RAID controller interface for instance.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
@Icohan
trying my best to answer your questions

As the answer is very general -  can it be more specific? like connection type? iSCSI, fiber, etc...? RAID level? etc

Answer: DAS Model
DELL MD 3200 (3.5" chassis)  

Hard Drive Size
3.5 in. Hard Drives

Storage
DELL MD 3200u

RAID Type: RAID 5

FILE SYSTEM :NTFS

not sure i give you the right info you were looking for ,i dont have much knowledge about hardware .


i will run DBCC CHECKDB (DBnameHere) WITH PHYSICAL_ONLY;
 as you suggested .
Database Analyst
Commented:
Ok, so there should be a Storage Manager software that may be able to tell if there's any issue with any of the drives behind that RAID 5 for the S:\ drive right? Or if you(or someone else) has access to the physical storage - can it be checked please to see if any drives show different color than green on the PowerVault?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Above error can appear only if the data file size of that particular database is very high.
Kindly try creating file groups and reduce the size of individual data files to get the issue resolved out.
Kindly try the below links for more info..
https://support.microsoft.com/en-us/help/2002606/os-errors-1450-and-665-are-reported-for-database-data-files

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial