主页 / dba / 问题 / 154260
Accepted
Scott Hodgin - Retired
Asked: 2016-11-05 05:05:21 +0800 CST

有趣的 DBCC CHECKDB 场景

  • 772

我今天早上醒来发现我的一个数据库损坏了。我有一份工作，每天为服务器上的所有数据库运行 DBCC CHECKDB。输出表明只有一张表损坏（幸运的是）。

Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data). The previous link (5:216001) on page (5:216002) does not match the previous page (5:2603841) that the parent (5:3902159), slot 23 expects for this page.
Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data). B-tree chain linkage mismatch. (5:628894)->next = (5:628895), but (5:628895)->Prev = (5:2602254).
Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data). The previous link (5:1165706) on page (5:1165707) does not match the previous page (5:2602253) that the parent (5:1529), slot 33 expects for this page.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data). Index node page (5:2239068), slot 33 refers to child page (5:2602254) and previous child (5:628894), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data). Index node page (5:3902159), slot 22 refers to child page (5:2603841) and previous child (5:216001), but they were not encountered.
Msg 8981, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 10, partition ID 72057601280507904, alloc unit ID 72057601393229824 (type In-row data). The next pointer of (5:1212172) refers to page (5:3489726). Neither (5:3489726) nor its parent were encountered. Possible bad chain linkage.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data). Index node page (5:1529), slot 32 refers to child page (5:2602253) and previous child (5:1165706), but they were not encountered.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 10, partition ID 72057601280507904, alloc unit ID 72057601393229824 (type In-row data). Page (5:3489727) is missing a reference from previous page (5:1212172). Possible chain linkage problem.
Msg 2533, Level 16, State 1, Line 1
Table error: page (5:2602253) allocated to object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (5:2602254) allocated to object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (5:2602255) allocated to object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (5:2603840) allocated to object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (5:2603841) allocated to object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (5:2603842) allocated to object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data). The previous link (5:2602255) on page (5:15918) does not match the previous page (5:15917) that the parent (5:3904979), slot 8 expects for this page.
Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data). B-tree chain linkage mismatch. (5:15917)->next = (5:15918), but (5:15918)->Prev = (5:2602255).
CHECKDB found 0 allocation errors and 16 consistency errors in table 'AperioCompare.PolicyAmendmentCompare' (object ID 1946163866).
CHECKDB found 0 allocation errors and 16 consistency errors in database 'Aperio'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Aperio).

我一直在寻找最后一行，这样我就可以弄清楚所涉及的最低恢复水平。在这种情况下，它表示“repair_allow_data_loss 是 DBCC CHECKDB 发现的错误的最低修复级别”。

我查看了对象 ID 并验证了 OBJECT_NAME(1946163866) 确实指向了我的 ONE 表。错误报告显示索引 ID 10 和 11 存在问题。然后我从 sys.indexes 中选择 object_id=1946163866 并找到索引 10 和 11 的索引行——这两个索引都是非聚集的。我删除并重新创建了两个非聚集索引并重新运行我的 DBCC CHECKDB - 没有报告任何错误。

我对 DBCC CHECKDB 报告的最低恢复级别 repair_allow_data_loss 感到困惑。这不应该重建吗？

我在这里遗漏了一些明显的东西吗？

dbcc sql-server-2016

2 个回答

  1. 那么你正在删除数据，即使它只是索引。;-) 然而，删除非聚集索引不会导致数据丢失。

    stackoverflow 上有一篇文章可以为您提供更多信息：
    使用 REPAIR_ALLOW_DATA_LOSS 时会丢失什么（种类）数据？

    它引用了 Paul S. Randall 的 SQLSkills.com 网站上的一篇文章，他在一篇文章中写道：

    REPAIR_ALLOW_DATA_LOSS 是 DBCC CHECKDB 在发现损坏时建议的修复级别。这是因为修复几乎所有不是次要的非聚集索引问题的东西都需要删除一些东西来修复它。因此，REPAIR_ALLOW_DATA_LOSS 将删除内容。这意味着它也可能会删除您的一些数据。例如，如果它在数据页上发现损坏的记录，它可能最终不得不删除整个数据页，包括页面上的所有其他记录，以修复损坏。那可能是很多数据。出于这个原因，修复级别的名称是经过精心选择的。您不能键入 REPAIR_ALLOW_DATA_LOSS 而不意识到作为操作的一部分您可能会丢失一些数据。

    短的：

    这是因为修复几乎所有不是次要的非聚集索引问题的东西都需要删除一些东西来修复它。

    所以你有它。如果它不是非聚集索引，您将丢失数据。

    手动修复索引问题然后重新运行DBCC checkdb命令可能是最好的选择，因为您可以控制数据丢失。如果在修复索引后您仍然有损坏的数据，那么您必须找出数据的重要性以及是否必须将数据库还原到不同的服务器以恢复损坏的数据。

    顺便说一句：Paul 在 Microsoft SQL Server 开发团队中负责 DBCC CHECKDB 命令回到 SQL Server 2000 版并优化了 2005 版Bio中的所有内容

    • 1
  2. Best Answer

    是的 - CHECKDB 选择 REPAIR_ALLOW_DATA_LOSS 时存在一些错误，即使它是非聚集索引。您应该能够手动重建这些（开始 tran、禁用、重建、提交 tran）。我的猜测是丢失的写入、过时的读取，或者可能是 2016 年的新错误。

    • 1

相关问题