What Part of the SQLDump Suggest Database Corruption? – Interview Question of the Week #188

Question: What part of a SQLDump indicates database corruption?

Answer: In this example, the evidence is the consistency-check error and the DBCC CHECKDB summary inside the text dump. A file named SQLDump on its own is not a diagnosis of database corruption.

My friend Kevin, @Kevin3NF, asked a useful follow-up on Too Many SQLDump Files Consuming a Lot of Disk Space. I had said the example was due to corruption. Kevin asked: which part of the dump tells us that?

Open SQLDump<nnnn>.txt and examine the DbccResults section. This is the relevant excerpt, with spacing added for readability; the ellipsis represents omitted entries:

<DbccResults>
  <Dbcc ID="0" Error="8939" Severity="16" State="98">
    Table error: Object ID 0, index ID -1, partition ID 0,
    alloc unit ID 281492156579840 (type Unknown), page (0:0).
    Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed.
    Values are 133129 and -4.
  </Dbcc>
  ...
  <Dbcc ID="954" Error="8957" Severity="-1" State="1">
    DBCC CHECKDB (PRODDB) executed by sa found 806 errors
    and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.
    Internal database snapshot has split point LSN = 00000231:000001d8:0001
    and first LSN = 00000231:000001c8:0001.
  </Dbcc>

The first entry is an actual consistency-check failure. The summary is the decisive context: CHECKDB examined PRODDB, found 806 errors, and repaired none. That is much stronger evidence than recognizing a dump extension or seeing the word “error” somewhere in a log.

The matching .log and SQL Server ERRORLOG can provide more context. The .mdmp may be useful for deeper investigation, especially with Microsoft support, but you do not have to decode the binary dump to read the consistency-check result shown here.

Other dumps can arise from assertions, access violations or other failures. Read the actual message and its surrounding context before deciding on the cause. If CHECKDB reports consistency errors, preserve the diagnostics and investigate the database and storage. A repair that allows data loss is not the first response; assess a verified clean restore path before choosing a recovery action.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.