第10回では「整合性チェック」についてまとめてみました。今回は、SQL Serverの整合性チェック (DBCC CHECKDB) で不整合が検知された場合の対処方法 について、自分の整理も兼ねて、まとめてみようと思います。
- 整合性チェック実行結果例について (前回のおさらい)
- インデックス ID の種類について
- 整合性チェック エラーの最小修復レベルについて
- DBCC CHECKDB コマンドを使用したデータ修復方法について
- まとめ
整合性チェック実行結果例について (前回のおさらい)
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 がエラー メッセージを出力した場合は、システム管理者に相談してください。 |
確認ポイント
- 不整合が発生しているオブジェクト： テーブル ’tab1'
- 不整合が発生しているインデックス ID : すべて インデックス ID 2
- エラーの最小修復レベル : repair_allow_data_loss
「インデックス ID」、「エラーの最小修復レベル」の各々について、以下に整理してみます。
インデックス ID の種類について
インデックス の種類により ID が異なっており、以下のように設定されています。
- インデックス ID 0 : ヒープ
- インデックス ID 1 : クラスター化インデックス
- インデックス ID 2 以上 : 非クラスタ化インデックス
整合性チェック エラーの最小修復レベルについて
DBCC CHECKDB コマンドを実行し、 データベースに不整合が検知された場合、エラーの最小修復レベルが一般的に出力されます。
エラーの最小修復レベル「REPAIR_ALLOW_DATA_LOSS」
データベースを物理的に一貫性のある状態に修復する必要があるエラーレベルであり、本修正レベルで修復処理を実行した場合、データが失われる可能性があることを示している。
エラーの最小修復レベル「REPAIR_REBUILD」
データベースを物理的に一貫性のある状態に修復する必要があるエラーレベルであることは「REPAIR_ALLOW_DATA_LOSS」と同じですが、本修正レベルで修復処理を実行したとしても、データが失われることがないことを示している。
DBCC CHECKDB コマンドを使用したデータ修復方法について
データベースのデータを修復する際、以下のような３つの方法について検討することが多いかと思います。
- 不整合が発生する前のバックアップからデータベースを復元
- 不整合が発生しているデータベースからデータを抜き出し、抜き出したデータをもとに新しいデータベースを作成
- DBCC CHECKDB コマンドをデータベース修復オプション(「REPAIR_REBUILD」、「REPAIR_ALLOW_DATA_LOSS」) を指定して、不整合の発生したデータページの修復を実施
不整合が発生する前のバックアップからデータベースを復元
不整合が発生したデータページを確実に修復する方法として、不整合が発生する前のバックアップから復元することが、最も確実な方法になります。
DBCC CHECKDB コマンドの出力結果で、インデックス ID ０ (ヒープ) もしくは、インデックス ID 1 (クラスタ化インデックス) で不整合が検出された場合、 実データを含んでいるため、DBCC CHECKDB コマンドをデータベース修復オプションを指定して修復できたとしても、どのデータが損失するかをデータベースの機能として特定することはできないため、修復したオブジェクト (オブジェクト) のデータの整合性については、アプリケーションで保持しているジャーナル情報などからデータの整合性を確認するなどのコストの掛かる作業が必要になります。
不整合が発生しているデータベースからデータを抜き出し、抜き出したデータをもとに新しいデータベースを作成
DBCC CHECKDB コマンドの出力結果で、インデックス ID ０ (ヒープ) もしくは、インデックス ID 1 (クラスタ化インデックス) で不整合が検出された場合でかつ、不整合が発生する前のバックアップが存在しなかった場合、不整合が発生しているデータベースからデータを抜き出し、抜き出したデータをもとに新しいデータベースを作成することで、不整合を解消することが可能になります。
DBCC CHECKDB コマンドをデータベース修復オプション(「REPAIR_REBUILD」、「REPAIR_ALLOW_DATA_LOSS」) を指定して、不整合の発生したデータページの修復を実施
DBCC CHECKDB コマンドの出力結果より、エラーの最小修復レベルを確認し、「REPAIR_REBUILD」であった場合、DBCC CHECKDB コマンド によりデータベースの不整合をデータを損失することなく修復することが可能になります。 しかしながら、「REPAIR_ALLOW_DATA_LOSS」であった場合、かつ、インデックス ID ０ (ヒープ) もしくは、インデックス ID 1 (クラスタ化インデックス) で不整合が検出されている場合、データを損失することになり、また、システム オブジェクトで不整合が発生していた場合、不整合が解消されたとしても、正常に SQL Server が動作することが保証されない状態になります。
そのため、エラーの最小修復レベルが「REPAIR_ALLOW_DATA_LOSS」の場合は、「不整合が発生する前のバックアップから復元する」 もしくは、「不整合が発生しているデータベースからデータを抜き出し、抜き出したデータをもとに新しいデータベースを作成」することをまず初めに検討すると良いかと思います。
なお、エラーの最小修復レベルが「REPAIR_ALLOW_DATA_LOSS」であった場合においても、インデックス ID 2 以上 (非クラスタ化インデックス) でのみ不整合が検出されている場合は、実データを損失することがないため、DBCC CHECKDB コマンドで不整合の発生したデータベースを修復することを検討されても良いかと思います。
まとめ
今回は、 SQL Serverの整合性チェック (DBCC CHECKDB) で不整合が検知された場合の対処方法についてまとめてみました。DBCC CHECKDB コマンドを修復オプションを指定して実行することで、データベースの不整合を修復できる可能性はありますが、確実に不整合を修復できることは保証されていないため、インデックス ID 2 以上 (非クラスタ化インデックス) のみの不整合、および、エラーの最小修復レベルが「REPAIR_REBUILD」以外の場合は、DBCC CHECKDB コマンドを使用した修復以外の方法をまずは検討することをお勧めします。
次回は、DBCC CHECKDB コマンドを使用した不整合の解消方法、不整合の発生したデータベースからデータを抜き出す方法について、まとめてみようと思います。
【第12回】基本から始める SQL Server【整合性チェック 不整合検知後の対処方法2】へ
