So my regular DBCC check had this lovely message waiting for me in the error logs this morning. I've been trying to find out more about this issue and what can be done about it. Manually running DBCC CHECKDB will result in DBCC aborting with Error 6 (connection closed by remote host) and get me this error in the SQL log file. Running DBCC CHECKDB(dbname) WITH PHYSICAL ONLY or DBCC CHECKDB(dbname, NOINDEX) will complete successfully and report no errors. Chkdsk reports no errors on the drive.
Both the drive and the database have ample free space. The disk is on a SAN (rather than local).
How can I determine if this a corruption error in the database file or an actual "on the disk" bad sector? Can this be fixed in place or do I need a new disk and to restore from backups? While I have good backups, if I restore a recent backup, will that also "restore" the missing data/sector?
FWIW the DB is 2.2 TB so I'm rather reluctant to jump into a fix until I really know what's going on.
EDIT: This morning, things went even further sideways. We had a log full error, but the log could not be extended or backed up due to the same disk error (sector cannot be found). This was preventing any actions in the database or it's related application. Had to back up the log to another drive to free up log space and allow for normal operations to continue.
Best Answer
Turns out the problem had to do with the SAN returning invalid sectors during disk IO events. What had happened was that the disk had had 400Gb of unusable space removed, and something went sideways in the disk definition in the SAN. The solution was to shrink the disk by 10Gb then grow back that 10Gb to reestablish the disk definition in the SAN. Once that was complete, suddenly everything was fine. Not at all what I expected but I wanted to show what the solution was, because I think it's important that "rock solid, never has an error, why are you even looking at that" systems need to be included in a full error analysis.