Our schedule CHECKDB failed today for out largest DB (4TB). Attempting to isolate the problem I start with the index filegroup and ran
DBCC CHECKFILEGROUP (N'NCIndexes') WITH PHYSICAL_ONLY, NO_INFOMSGS, ALL_ERRORMSGS
This worked just fine, when i removed the PHYSICAL_ONLY I got the following errors:
Msg 5269, Level 16, State 1, Line 1
Check terminated. The transient database snapshot for database 'MYDB' (database ID 7) has been marked suspect due to an IO operation failure. Refer to the SQL Server error log for details.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
A couple of things to note:
1. The window I was running this from lost its connection completely after the failure (i.e. no longer connected to the server)
2. There is no stack dump or further logging in the SQL Error other than
Error: 17053, Severity: 16, State: 1.
N:\CA_NCIndexes.ndf:MSSQL_DBCC23: Operating system error 1450(Insufficient system resources exist to complete the requested service.) encountered.
The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x000063064d0000 in file 'N:\CA_NCIndexes.ndf:MSSQL_DBCC23'. 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.
And then a informational message got printed out:
DBCC CHECKFILEGROUP (COREACQUIRE) WITH all_errormsgs, no_infomsgs executed by FLEETONE\miraultsj found 0 errors and repaired 0 errors. Elapsed time: 2 hours 17 minutes 14 seconds. Internal database snapshot has split point LSN = 0006ffe5:0167d9d1:000b and first LSN = 0006ffe5:0167d5ba:0001.
______________________________________________________________________________"Never argue with an idiot; They'll drag you down to their level and beat you with experience"