DBCC CHECKDB出現2570一致性錯誤

    最近升級到SQL 2008 R2　x64版，在備份資料庫前加了資料完整性檢查的排程，隔天發現完整性檢查排程執行失敗，執行的指令如下

DBCC CHECKDB (test) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY

    依據DBCC CHECKDB裡錯誤報告一節指出，每當 DBCC CHECKDB 偵測到損毀錯誤時，都會在 SQL Server LOG 目錄中建立傾印檔案 (SQLDUMPnnnn.txt)。

    在SQL Server記錄檔裡也可以看到傾印檔案的位置，如下圖

    開啟傾印檔案會告訴你錯誤的資訊，你就可以根據這些資訊修正它，資訊擷取如下

DBCC RESULTS

--------------------                                                                            

頁面 (1:165309)，物件識別碼 1174347298 中的位置 12，索引識別碼 0，資料分割識別碼 76962024

521728，配置單位識別碼 76962024521728 (類型 "In-row data")。資料行 "COLUMN1" 值超出資料類型 "char" 的範圍。請將資料行更新為合法的值。

           

CHECKDB 發現了資料表 'TABLE1' (物件識別碼 1174347298) 中 0 個配置錯誤和 1 個一致性錯誤。

    若直接執行上述DBCC指令，若有錯誤也可以從輸出了解，上下兩個資訊一模一樣，只是中英文的差異囉

訊息2570，層級16，狀態3，行3

Page (1:165309), slot 12 in object ID 1174347298, index ID 0, partition ID 76962024521728, alloc unit ID 76962024521728 (type "In-row data"). Column "COLUMN1" value is out of range for data type "char".  Update column to a legal value.

CHECKDB found 0 allocation errors and 1 consistency errors in table 'TABLE1' (object ID 1174347298).

    錯誤碼2570是指資料行裡包含無效的值，此例是指TABLE1的COLUMN1欄位中有超出char長度的值，然後2570的錯誤是無法靠Repair選項來修復，只能手動更新，疑難排解在SQL Server 2005和更新版本的DBCC錯誤2570裡有提到怎麼修復一致性的錯誤，但沒有提到char或varcahr這種的資料型態，我只好在測試環境使用裡面所提到的第二種方法，執行如下指令找出Page裡的資料

--

DBCC TRACEON ( 3604 )

DBCC PAGE ( test , 1 , 165309 , 3 )

DBCC TRACEOFF  ( 3604 )

    從輸出結果找到對應的紀錄，錯誤訊息指出在Slot 12，擷取部分資料如下，你就會發現到某列COLUMN1竟顯示INVALID COLUMN VALUE

Slot 12 Column 9 Offset 0x51 Length 62 Length (physical) 62

COLUMN1 = INVALID COLUMN VALUE

Slot 12 Column 8 Offset 0x4e Length 3 Length (physical) 3

DTZIP = 106                         

    利用其他欄位相信可以找到那筆有無效值的欄位，如下圖

    可以發現看COLUMN1的結尾有個問號，但你看長度並沒有超過60，所以想用len來查出這筆資料並不可行，但可以用簡單的LIKE指令找到這筆有＂?＂的資料喔

SELECT * FROM TABLE1 where COLUMN LIKE '%?'