遭遇 `DBCC CHECKDB` 報錯 `Msg 8929` / `Msg 8961` 指向系統表 `sysobjects` (Object ID 2) 的 LOB 結構不匹配,屬於罕見但嚴重的元資料損壞。本文記錄從錯誤解讀、定位受影響用戶表、數據遷移修復到驗證清零的完整實戰過程。
Msg 8929, Level 16, State 1:
Object ID 2: Error found in text ID 177078272 owned by data record RID = (1:627:1) id = 1899153811 indid = 10.
Msg 8961, Level 16, State 1:
Table error: Object ID 2. text/ntext/image node at (1:875, slot 0, text ID 177078272)
does not match reference at (1:500, slot 14).
CHECKDB found 0 allocation errors and 2 consistency errors in table 'sysindexes' (Object ID 2).
SELECT OBJECT_NAME(1899153811); -- 返回具體用戶表名,假設為 dbo.TroubleTable
SELECT * INTO dbo.TroubleTable_Bak FROM dbo.TroubleTable;
TRUNCATE TABLE dbo.TroubleTable;
此時再執行 `DBCC CHECKDB` → **錯誤消失** (系統表 LOB 引用已隨分配單元釋放而修正)
INSERT INTO dbo.TroubleTable (Col1, Col2, ...)
SELECT Col1, Col2, ... FROM dbo.TroubleTable_Bak;
DBCC CHECKDB ([MyDB]) WITH NO_INFOMSGS;
-- 返回:CHECKDB found 0 allocation errors and 0 consistency errors.
| 層面 | 措施 |
|---|---|
| 定期巡檢 | 每日 `DBCC CHECKDB` (生產庫) / 週期性 (大型庫 `PHYSICAL_ONLY`) |
| 異常即警報 | Agent Job 封裝 `DBCC CHECKDB` → 解析輸出 → 郵件/企業微信告警 |
| LOB 型別治理 | 新開發禁用 `text/ntext/image`,改用 `varchar(max)/nvarchar(max)/varbinary(max)` |
| 頁面校驗和 | `ALTER DATABASE SET PAGE_VERIFY CHECKSUM` (預設開啟) |
| 備份驗證 | `RESTORE VERIFYONLY` + 定期演練還原 |