sqlserver 關於DBCC CHECKDB的總結

官方文件

透過執行下列操作檢查指定資料庫中所有物件的邏輯和物理完整性：

    對資料庫執行 DBCC CHECKALLOC。

    對資料庫中的每個表和檢視執行 DBCC CHECKTABLE。

    對資料庫執行 DBCC CHECKCATALOG。

    驗證資料庫中每個索引檢視的內容。

    使用 FILESTREAM 在檔案系統中儲存 varbinary(max) 資料時，驗證表後設資料和檔案系統目錄和檔案之間的連結級一致性。

    驗證資料庫中的 Service Broker 資料。

DBCC CHECKDB 其實主要做兩件事情：

    檢查資料庫裡有沒有損壞發生(不檢查禁用的索引)

    盡力修復資料庫損壞，使資料庫能夠被重新正常訪問。

DBCC CHECKDB 最佳實踐

建議對生產系統頻繁使用 PHYSICAL_ONLY 選項。 使用 PHYSICAL_ONLY 可以極大地縮短對大型資料庫執行 DBCC CHECKDB 的執行時間。 同時建議您定期執行沒有選項的 DBCC CHECKDB。 應當以什麼頻率執行這些執行任務將取決於各個企業及其生產環境。

DBCC CHECKDB修復引數

示例：DBCC CHECKDB ('db_name', REPAIR_FAST);

1. REPAIR_ALLOW_DATA_LOSS 嘗試修復報告的所有錯誤。 這些修復可能會導致一些資料丟失。

2. REPAIR_FAST 保留該語法只是為了向後相容。 未執行修復操作。

3. REPAIR_REBUILD，執行不會丟失資料的修復。 這包括快速修復（如修復非聚集索引中缺少的行）以及更耗時的修復（如重新生成索引）。此引數不修復涉及 FILESTREAM 資料的錯誤。

DBCC CHECKDB是否加鎖

DBCC CHECKDB預設不加鎖而是工作在一個隱藏的資料庫快照，執行DBCC CheckDB時指定了TABLOCK選項才會加鎖

DBCC CHECKDB引數說明

ALL_ERRORMSGS：顯示針對每個物件報告的所有錯誤。 預設情況下顯示所有錯誤訊息。

EXTENDED_LOGICAL_CHECKS：如果相容性級別為 100 (SQL Server 2008) 或更高，則對索引檢視、XML 索引和空間索引（如果存在）執行邏輯一致性檢查。

NO_INFOMSGS：取消顯示所有資訊性訊息。

NOINDEX：指定不應對使用者表的非聚集索引執行會佔用很大系統開銷的檢查。 這將減少總執行時間。 NOINDEX 不影響系統表，因為總是對系統表索引執行完整性檢查。

PHYSICAL_ONLY：將檢查限制為頁和記錄標頭的物理結構完整性以及資料庫的分配一致性。 設計該檢查是為了以較小的開銷檢查資料庫的物理一致性，但它還可以檢測會危及使用者資料安全的殘缺頁、校驗和錯誤以及常見的硬體故障。因此，使用 PHYSICAL_ONLY 選項可能會大幅減少對較大資料庫執行 DBCC CHECKDB 所需的時間，所以對需要頻繁檢查的生產系統，建議使用此選項。我們仍然建議完整地定期執行 DBCC CHECKDB。

ESTIMATEONLY：顯示執行包含所有其他指定選項的 DBCC CHECKDB 時所需的 tempdb 空間估計量。 不執行實際資料庫檢查。

DATA_PURITY：使 DBCC CHECKDB 檢查資料庫中是否存在無效或越界的列值。

TABLOCK：使 DBCC CHECKDB 獲取鎖，而不使用內部資料庫快照。 這包括一個短期資料庫排他 (X) 鎖。 TABLOCK 可使 DBCC CHECKDB 在負荷較重的資料庫上執行得更快，但 DBCC CHECKDB 執行時會減少資料庫上可獲得的併發性。

DBCC CHECKDB 錯誤訊息

DBCC CHECKDB 命令結束之後，便會將一個訊息寫入 SQL Server 錯誤日誌。 如果 DBCC 命令成功執行，則訊息指示成功以及命令的執行時間。 如果 DBCC 命令在完成檢查之前由於錯誤而停止，則訊息將指示命令已終止，並指示狀態值和命令執行的時間。 下表列出並說明了此訊息中可包含的狀態值。

State 描述

0 出現錯誤號 8930。 這表示後設資料中存在的損壞終止了 DBCC 命令。

1 出現錯誤號 8967。 存在一個內部 DBCC 錯誤。

2 在緊急模式資料庫修復過程中出錯。

3 這表示後設資料中存在的損壞終止了 DBCC 命令。

4 檢測到斷言或訪問違規。

5 出現終止了 DBCC 命令的未知錯誤

sp_MSforeachDB

sp_MSforeachdb是微軟提供的不公開的儲存過程，儲存在master資料庫中。可以用來對某個資料庫的所有表或某個SQL伺服器上的所有資料庫進行管理，下面將對此進行詳細介紹。

sp_MSforeachDB使用DBCC CHECKDB的示例：

use master

exec sp_MSforeachDB 'DBCC CHECKDB ([?]) WITH ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY,NO_INFOMSGS'

其中?表示萬用字元，表示所有資料庫

DBCC CHECKDB 遇到的一些錯誤及分析

1、There is insufficient memory available in the buffer pool. [SQLSTATE 42000] (Error 802)  During undoing of a logged operation in database 'HistoryDB', an error occurred at log record ID (5106285:51843537:99). Typically, the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup, or repair the database. [SQLSTATE 42000] (Error 3314)  A database snapshot cannot be created because it failed to start.

原因分析：報錯很明顯：緩衝池中沒有足夠的可用記憶體。檢查是否資料庫HistoryDB特別大導致，如果是則在DBCC CHECKDB時加上PHYSICAL_ONLY 選項

2、Object ID 34 (object 'sys.sysschobjs'):  DBCC could not obtain a lock on this object because the lock request timeout period was exceeded.  This object has been skipped and will not be processed. 

原因分析：當我看到這個錯誤時，我問自己一個問題:“DBCC CHECKDB執行鎖嗎?”答案是否定的。從SQL Server 2005開始，DBCC CheckDB工作在一個隱藏的資料庫快照上。資料庫快照是資料庫的只讀副本。由於快照I/O開銷，您可以看到伺服器上出現了一些阻塞，或者使用者速度較慢，但肯定沒有鎖。這個時候要檢查自己的DBCC CHECKDB是否加了TABLOCK選項，如果是則取消TABLOCK選項