第9回では「チェックポイント」についてまとめてみました。今回は SQL Serverの 整合性チェック (DBCC CHECKDB) について、自分の整理も兼ねて、まとめてみようと思います。
整合性チェックについて
整合性チェック (以下 DBCC CHECKDB) コマンドは、指定したデータベース内のすべてのオブジェクトの論理的、物理的な整合性のチェックを実施するコマンドであり、SQL Server で唯一 データベースに不整合がないかを確認可能なコマンドになります。
DBCC CHECKDB コマンドを実行した際、内部的にデータベースのスナップショットを生成することで、オンライン (ユーザー トランザクション (DMLクエリなど) が実行されている状態) で実行することが可能です。
また、DBCC CHECKDB コマンドには、「TABLOCK」、「ESTIMATEONLY」、「PHYSICAL_ONLY」などのオプションが用意されています。
※ DBCC CHECKDB コマンドの実行時間を短くしたい場合、「TABLOCK」オプションを指定して実行することで、内部的にデータベースのスナップショットを作成する代わりに処理の中で排他ロック (Xロック) が獲得されることになりますが、処理時間が短くなることが期待できます。また、「PHYSICAL_ONLY」を指定することで、ページとレコード ヘッダーの物理構造の整合性、データベースへの割り当ての一貫性チェックにチェック内容を限定することで、処理時間が短くなることが期待できます。
DBCC CHECKDB コマンドの詳細については、以下 URL を参照。
整合性チェックで実行されている処理について
DBCC CHECKDB コマンド (既定) では、以下のような処理が実行されます。
- データベースに割り当てられたディスク領域の構造に関する一貫性チェック (DBCC CHECKALLOC コマンド)
- データベース内のすべてのテーブルとビューを構成するページ、ページ構造の整合性チェック (DBCC CHECKTABLE コマンド)
- データベース内のカタログに関する一貫性チェック (DBCC CHECKCATALOG)
- データベース内のすべてのインデックス付きビューの内容検証
※ 「DBCC CHECKALLOC コマンド」、「DBCC CHECKTABLE コマンド」、「DBCC CHECKCATALOG」については、DBCC CHECKDB コマンド (既定) を実行することで内部的に実行されていますが、各コマンドを個別に実行することが可能になります。
例えば、データ量の多いテーブルが複数存在する場合、曜日毎に「DBCC CHECKTABLE コマンド」で実行するテーブルを指定したり、各テーブルに作成されたインデックス単位でコマンドを実行することで、整合性チェックに要する時間を短くすることが可能になります。
整合性チェック実行結果例について
DBCC CHECKDB コマンドを実行し、 データベースに不整合がある場合、以下のような結果が出力されます。
| dbcc checkdb('TESTDB') go |
| メッセージ 8944、レベル 16、状態 12、行 30 テーブル エラー: オブジェクト ID 1557580587、インデックス ID 2、パーティション ID 72057594043629568、アロケーション ユニット ID 72057594050117632 (型 In-row data)、ページ (3:17753)、行 9。テスト (ColumnOffsets <= (nextRec - pRec)) が失敗しました。値は 17920 と 108 です。 メッセージ 8976、レベル 16、状態 1、行 30 テーブル エラー: オブジェクト ID 1557580587、インデックス ID 2、パーティション ID 72057594043629568、アロケーション ユニット ID 72057594050117632 (型 In-row data)。ページ (3:17753) がスキャンでは見つかりませんでしたが、このページは親ページ (3:18128) と前ページ (3:17754) から参照されています。以前に発生したエラーをすべて確認してください。 メッセージ 8944、レベル 16、状態 12、行 30 テーブル エラー: オブジェクト ID 1557580587、インデックス ID 2、パーティション ID 72057594043629568、アロケーション ユニット ID 72057594050117632 (型 In-row data)、ページ (3:17753)、行 9。テスト (ColumnOffsets <= (nextRec - pRec)) が失敗しました。値は 17920 と 108 です。 メッセージ 8928、レベル 16、状態 1、行 30 オブジェクト ID 1557580587、インデックス ID 2、パーティション ID 72057594043629568、アロケーション ユニット ID 72057594050117632 (型 In-row data): ページ (3:17753) を処理できませんでした。詳細については、他のエラーを参照してください。 'tab1' の DBCC 結果。 オブジェクト "tab1" の 705 ページには 50007 行あります。 CHECKDB により、テーブル 'tab1' (オブジェクト ID 1557580587) に 0 個のアロケーション エラーと 4 個の一貫性エラーが見つかりました。 CHECKDB により、データベース 'TESTDB' に 0 個のアロケーション エラーと 4 個の一貫性エラーが見つかりました。 repair_allow_data_loss は DBCC CHECKDB (TESTDB) で見つかったエラーの最小修復レベルです。 DBCC の実行が完了しました。DBCC がエラー メッセージを出力した場合は、システム管理者に相談してください。 |
上記の結果例では、データベース ’TESTDB' 上のテーブル ’tab1' 上に作成した非クラスタ化インデックスを構成するデータページをわざと破損させたので、DBCC CHECKDB コマンドによって、不整合が検知されています。
なお、DBCC CHECKDB コマンドの出力結果で確認するポイントは、「どのオブジェクトで不整合が発生しているか」、「不整合が発生しているインデックスIDは何か」、「エラーの最小修復レベルは何か」を明確にすることです。
今回の場合は、
- 「どのオブジェクトで不整合が発生しているか」： テーブル ’tab1'
- 「不整合が発生しているインデックスIDは何か」： 不整合はすべて インデックス ID 2 で発生
- 「エラーの最小修復レベルは何か」： repair_allow_data_loss
となります。
まとめ
今回は、SQL Serverの 整合性チェック (DBCC CHECKDB) についてまとめてみました。データベースで不整合が発生した場合、早めに対処しなければ、不整合の状態が悪化することになります。
整合性チェック (DBCC CHECKDB) は、メンテナンスプランから定期的に実行することが可能となっているため、システム運用形態 (24*365)、データベースサイズなどに依存するかと思いますが、可能な限り、短い間隔 (例：毎日実施するなど) で整合性チェック(DBCC CHECKDB) を実行することを検討すると良いかと思います。
次回は、整合性チェック (DBCC CHECKDB) で不整合が検知された場合の対処方法例について、まとめてみようと思います。
【第11回】基本から始める SQL Server【整合性チェック 不整合検知後の対処方法1】へ
