今回は [データベースの整合性確認] について振り返っていきたいと思います。
[メンテナンス プラン ウィザード] では、 [データベースの整合性確認] のメンテナンス タスクを作成することが可能です。
このタスクを実行するとデータベースの整合性をチェックすることができます。
セミナーの中では、バックアップの取得前等に実行をすることでデータベースに問題が無いかを確認しバックアップを取得することが可能というようなお話がありました。
今回は、この整合性確認についてまとめていきたいと思います。
■データベースの整合性確認で実行される内容
まずはデータベースの整合性確認のタスクで実行される内容について確認していきたいと思います。
作成したデータベースの整合性確認のタスクで実行される T-SQL を確認してみます。
データベースの整合性確認のタスクでは、実行対象として指定したデータベースに対して [DBCC CHECKDB] が実行されます。
# [インデックスを含める] を外すと [NOINDEX] オプションを指定して実行されます。
以下は、BOL の DBCC CHECKDB の記載内容の引用になります。
次の操作を実行し、指定したデータベース内のすべてのオブジェクトの論理的および物理的な整合性をチェックします。
- データベースに対して DBCC CHECKALLOC を実行。
- データベース内にあるすべてのテーブルとビューに対して DBCC CHECKTABLE を実行。
- データベースに対して DBCC CHECKCATALOG を実行。
- データベース内にあるすべてのインデックス付きビューの内容を検証。
- FILESTREAM を使用して varbinary(max) データをファイル システムに格納する場合のテーブルのメタデータとファイル システムのディレクトリおよびファイルの間のリンクレベルの一貫性を検証。
- データベースの Service Broker データを検証。
DBCC CHECKDB では、上記の処理を実行してデータベースの整合性が取れているかを確認する処理になります。
■整合性確認を実施しないとどうなるか
データベースの整合性確認を行う事でどのようなことが分かるか、簡単に試してみたいと思います。
今回は、バイナリエディタでデータファイル (mdf) を開き、一部のデータを書き換え、データの内容とページのチェックサムが一致しないようにしてみました。
データを書き換える前のテーブルの全件を取得した情報がこちらになります。
SQL Server 2008 以降であれば、データがどのページに格納されているかは [sys.fn_PhysLocFormatter(%%physloc%%)] を使用することで確認することができます。
# この関数は、Undocument の関数です。
上のデータは、以下のクエリを実行して取得しています。
|
SELECT
sys.fn_PhysLocFormatter(%%physloc%%) AS [PageNo],
*
FROM
Table_1
|
SQL Server のページにはチェックサムがあり、データの内容を元に計算したチェックサムとデータの内容が一致しているかを確認することで、ページが破損しているかを検出する機能がありますのでページに一貫性が無いとエラーとして検出することができます。
# データベースオプションで [CHECKSUM] / [TORN_PAGE_DETECTION] / [NONE] のいずれかを設定することが可能です。([TORN_PAGE_DETECTION] は下位互換となっていますが。)
今回はバイナリエディタでデータベースのファイルを開いて、 (1:114:0) のページを壊してみました。
ページを壊した後に、データを全件取得すると以下のデータまでは取得できるのですが、 (1:114:0) のデータを読み込もうとした際にエラーが発生します。
|
メッセージ 824、レベル 24、状態 2、行 1
SQL Server で、一貫性に基づいた論理 I/O エラーが検出されました: 正しくないチェックサム (必要なチェックサム: 0x8b1229d0、実際のチェックサム: 0x831a29d0)。
このエラーは、ファイル ‘F:SQL2008R2TEST.mdf’ のオフセット 0x000000000e4000 にあるデータベース ID が 5 のページ (1:114) の 読み取り 中に発生しました。
SQL Server エラー ログまたはシステム イベント ログ内の別のメッセージで詳細情報が報告されることもあります。
このエラー状態は深刻で、データベースの整合性を損なう可能性があるので、すぐに解決する必要があります。
完全なデータベース一貫性確認 (DBCC CHECKDB) を実行してください。
このエラーには多くの要因があります。
詳細については、SQL Server オンライン ブックを参照してください。
|
(1:114) という情報が表示されていますので、ファイル ID 1 (mdf ファイル) の 114 ページに一貫性のエラー (チェックサムとデータが不整合) が発生していることが確認できます。
今回、私が壊したページ番号と一致しますね。
破損ページの操作をした場合、イベント ビューアーのアプリケーションにも同様の情報が出力されますので、監視ソフト等でログ監視をしているのであれば、エラーを検知することもできます。
SQL Server のログにも情報は出力されています。
今回はデータを全件読み込みしたので破損ページも読み込まれエラーが発生しましたが、破損したページにアクセスをしなければ正常にデータを読み込むことができます。
# Col = 4 のデータを読もうとするとエラーになってしまうので飛ばしています。(先行読み込みの可能性が)
破損したページを操作しないとエラーにはなりませんので、データベースに異常があるかは分かりません。
DBCC CHECKDB を定期的に実行することで障害により発生した潜在的に潜んでいるデータベースの異常をチェックすることが可能です。
[DBCC CHECKDB(N’TEST’)] を実行して、今回整合性のエラーが発生している [TEST] データベースでデータベースのチェックをしてみます。
|
‘TEST’ の DBCC 結果。
Service Broker メッセージ 9675、状態 1: 分析されるメッセージ型: 14。
Service Broker メッセージ 9676、状態 1: 分析されるサービス コントラクト: 6。
Service Broker メッセージ 9667、状態 1: 分析されるサービス: 3。
Service Broker メッセージ 9668、状態 1: 分析されるサービス キュー: 3。
Service Broker メッセージ 9669、状態 1: 分析されたメッセージ交換のエンドポイント: 0。
Service Broker メッセージ 9674、状態 1: 分析されたメッセージ交換グループ: 0。
Service Broker メッセージ 9670、状態 1: 分析されるリモート サービス バインド: 0。
Service Broker メッセージ 9605、状態 1: 分析されたメッセージ交換の優先度: 0。
‘sys.sysrscols’ の DBCC 結果。
オブジェクト "sys.sysrscols" の 7 ページには 637 行あります。
‘sys.sysrowsets’ の DBCC 結果。
オブジェクト "sys.sysrowsets" の 1 ページには 93 行あります。
‘sys.sysallocunits’ の DBCC 結果。
オブジェクト "sys.sysallocunits" の 2 ページには 107 行あります。
‘sys.sysfiles1’ の DBCC 結果。
オブジェクト "sys.sysfiles1" の 1 ページには 2 行あります。
‘sys.syspriorities’ の DBCC 結果。
オブジェクト "sys.syspriorities" の 0 ページには 0 行あります。
‘sys.sysfgfrag’ の DBCC 結果。
オブジェクト "sys.sysfgfrag" の 1 ページには 2 行あります。
‘sys.sysphfg’ の DBCC 結果。
オブジェクト "sys.sysphfg" の 1 ページには 1 行あります。
‘sys.sysprufiles’ の DBCC 結果。
オブジェクト "sys.sysprufiles" の 1 ページには 2 行あります。
‘sys.sysftinds’ の DBCC 結果。
オブジェクト "sys.sysftinds" の 0 ページには 0 行あります。
‘sys.sysowners’ の DBCC 結果。
オブジェクト "sys.sysowners" の 1 ページには 14 行あります。
‘sys.sysprivs’ の DBCC 結果。
オブジェクト "sys.sysprivs" の 1 ページには 130 行あります。
‘sys.sysschobjs’ の DBCC 結果。
オブジェクト "sys.sysschobjs" の 1 ページには 56 行あります。
‘sys.syscolpars’ の DBCC 結果。
オブジェクト "sys.syscolpars" の 8 ページには 488 行あります。
‘sys.sysnsobjs’ の DBCC 結果。
オブジェクト "sys.sysnsobjs" の 1 ページには 1 行あります。
‘sys.syscerts’ の DBCC 結果。
オブジェクト "sys.syscerts" の 0 ページには 0 行あります。
‘sys.sysxprops’ の DBCC 結果。
オブジェクト "sys.sysxprops" の 0 ページには 0 行あります。
‘sys.sysscalartypes’ の DBCC 結果。
オブジェクト "sys.sysscalartypes" の 1 ページには 34 行あります。
‘sys.systypedsubobjs’ の DBCC 結果。
オブジェクト "sys.systypedsubobjs" の 0 ページには 0 行あります。
‘sys.sysidxstats’ の DBCC 結果。
オブジェクト "sys.sysidxstats" の 2 ページには 158 行あります。
‘sys.sysiscols’ の DBCC 結果。
オブジェクト "sys.sysiscols" の 2 ページには 309 行あります。
‘sys.sysbinobjs’ の DBCC 結果。
オブジェクト "sys.sysbinobjs" の 1 ページには 23 行あります。
‘sys.sysaudacts’ の DBCC 結果。
オブジェクト "sys.sysaudacts" の 0 ページには 0 行あります。
‘sys.sysobjvalues’ の DBCC 結果。
オブジェクト "sys.sysobjvalues" の 22 ページには 159 行あります。
‘sys.sysclsobjs’ の DBCC 結果。
オブジェクト "sys.sysclsobjs" の 1 ページには 16 行あります。
‘sys.sysrowsetrefs’ の DBCC 結果。
オブジェクト "sys.sysrowsetrefs" の 0 ページには 0 行あります。
‘sys.sysremsvcbinds’ の DBCC 結果。
オブジェクト "sys.sysremsvcbinds" の 0 ページには 0 行あります。
‘sys.sysxmitqueue’ の DBCC 結果。
オブジェクト "sys.sysxmitqueue" の 0 ページには 0 行あります。
‘sys.sysrts’ の DBCC 結果。
オブジェクト "sys.sysrts" の 1 ページには 1 行あります。
‘sys.sysconvgroup’ の DBCC 結果。
オブジェクト "sys.sysconvgroup" の 0 ページには 0 行あります。
‘sys.sysdesend’ の DBCC 結果。
オブジェクト "sys.sysdesend" の 0 ページには 0 行あります。
‘sys.sysdercv’ の DBCC 結果。
オブジェクト "sys.sysdercv" の 0 ページには 0 行あります。
‘sys.syssingleobjrefs’ の DBCC 結果。
オブジェクト "sys.syssingleobjrefs" の 1 ページには 146 行あります。
‘sys.sysmultiobjrefs’ の DBCC 結果。
オブジェクト "sys.sysmultiobjrefs" の 1 ページには 106 行あります。
‘sys.sysguidrefs’ の DBCC 結果。
オブジェクト "sys.sysguidrefs" の 0 ページには 0 行あります。
‘sys.syscompfragments’ の DBCC 結果。
オブジェクト "sys.syscompfragments" の 0 ページには 0 行あります。
‘sys.sysftstops’ の DBCC 結果。
オブジェクト "sys.sysftstops" の 0 ページには 0 行あります。
‘sys.sysqnames’ の DBCC 結果。
オブジェクト "sys.sysqnames" の 1 ページには 97 行あります。
‘sys.sysxmlcomponent’ の DBCC 結果。
オブジェクト "sys.sysxmlcomponent" の 1 ページには 99 行あります。
‘sys.sysxmlfacet’ の DBCC 結果。
オブジェクト "sys.sysxmlfacet" の 1 ページには 112 行あります。
‘sys.sysxmlplacement’ の DBCC 結果。
オブジェクト "sys.sysxmlplacement" の 1 ページには 18 行あります。
‘sys.sysobjkeycrypts’ の DBCC 結果。
オブジェクト "sys.sysobjkeycrypts" の 0 ページには 0 行あります。
‘sys.sysasymkeys’ の DBCC 結果。
オブジェクト "sys.sysasymkeys" の 0 ページには 0 行あります。
‘sys.syssqlguides’ の DBCC 結果。
オブジェクト "sys.syssqlguides" の 0 ページには 0 行あります。
‘sys.sysbinsubobjs’ の DBCC 結果。
オブジェクト "sys.sysbinsubobjs" の 1 ページには 3 行あります。
‘sys.syssoftobjrefs’ の DBCC 結果。
オブジェクト "sys.syssoftobjrefs" の 0 ページには 0 行あります。
‘sys.queue_messages_1977058079’ の DBCC 結果。
オブジェクト "sys.queue_messages_1977058079" の 0 ページには 0 行あります。
‘sys.queue_messages_2009058193’ の DBCC 結果。
オブジェクト "sys.queue_messages_2009058193" の 0 ページには 0 行あります。
‘sys.queue_messages_2041058307’ の DBCC 結果。
オブジェクト "sys.queue_messages_2041058307" の 0 ページには 0 行あります。
‘sys.filestream_tombstone_2073058421’ の DBCC 結果。
オブジェクト "sys.filestream_tombstone_2073058421" の 0 ページには 0 行あります。
‘sys.syscommittab’ の DBCC 結果。
オブジェクト "sys.syscommittab" の 0 ページには 0 行あります。
‘LockEscalationEvent’ の DBCC 結果。
オブジェクト "LockEscalationEvent" の 1 ページには 1 行あります。
‘Table_1’ の DBCC 結果。
メッセージ 8928、レベル 16、状態 1、行 1
オブジェクト ID 2137058649、インデックス ID 1、パーティション ID 72057594038976512、アロケーション ユニット ID 72057594042712064 (型 In-row data): ページ (1:114) を処理できませんでした。詳細については、他のエラーを参照してください。
メッセージ 8939、レベル 16、状態 98、行 1
テーブル エラー: オブジェクト ID 2137058649、インデックス ID 1、パーティション ID 72057594038976512、アロケーション ユニット ID 72057594042712064 (型 In-row data)、ページ (1:114)。テスト (IS_OFF (BUF_IOERR, pBUF->bstat)) が失敗しました。値は 12716041 と -4 です。
メッセージ 8976、レベル 16、状態 1、行 1
テーブル エラー: オブジェクト ID 2137058649、インデックス ID 1、パーティション ID 72057594038976512、アロケーション ユニット ID 72057594042712064 (型 In-row data)。ページ (1:114) がスキャンでは見つかりませんでしたが、このページは親ページ (1:93) と前ページ (1:110) から参照されています。以前に発生したエラーをすべて確認してください。
メッセージ 8978、レベル 16、状態 1、行 1
テーブル エラー: オブジェクト ID 2137058649、インデックス ID 1、パーティション ID 72057594038976512、アロケーション ユニット ID 72057594042712064 (型 In-row data)。ページ (1:115) に前ページ (1:114) からの参照がありません。チェーン リンケージに問題がある可能性があります。
オブジェクト "Table_1" の 9 ページには 9 行あります。
CHECKDB により、テーブル ‘Table_1’ (オブジェクト ID 2137058649) に 0 個のアロケーション エラーと 4 個の一貫性エラーが見つかりました。
CHECKDB により、データベース ‘TEST’ に 0 個のアロケーション エラーと 4 個の一貫性エラーが見つかりました。
repair_allow_data_loss は DBCC CHECKDB (TEST) で見つかったエラーの最小修復レベルです。
DBCC の実行が完了しました。DBCC がエラー メッセージを出力した場合は、システム管理者に相談してください。
|
DBCC CHECKDB を実行することで、データベースの状態をチェックしてくれますので、破損している領域を操作しなくても異常があるかどうかの確認をしてくれます。
イベントビューアーと SQL Server のログにも結果の概要が出力されますので、後で結果を再確認することも可能です。
SQL Server のダンプのテキストも出力されています。
[msdb..suspect_pages] を SELECT することで直近の情報を取得することもできます。
DBCC CHECKDB をバックアップ実行前に実施する理由ですが、[障害が発生しているデータベースのバックアップは障害が発生した状態] となるからです。
障害が発生していない状態のデータベースのバックアップがないと破損している領域を修復することができません。
# SQL Server 2008 以降のデータベースミラーリングであれば、破損ページを読み込んだ際にミラー側から修復することも可能ですが。
そのため、バックアップを取得する前にデータベース自体に異常が無いかを確認して、その後にバックアップを取ることが推奨されます。
セミナーの中で質問 / 回答があったのですが、DBCC CHECKDB によるデータベースの整合性確認はデータベースのサイズが大きいとかなり負荷がかかります。
そのため、バックアップのたびに毎回実行するのが難しいかもしれませんね。
次の投稿では [データベースの整合性確認の負荷] についてまとめてみたいと思います。
