SQL に関する Q&A
破損から回復するためのヒント、データベースの圧縮についてのアドバイスなど
Paul S. Randal
Q. バックアップ戦略として、毎日午前 1 時に完全バックアップを実行し、ログのバックアップは 1 時間おきに実行しています。また、毎日午前 4 時に DBCC CHECKDB も実行しています。午前 8 時の出勤時に、深夜に行った整合性チェックで大規模な破損が検出されたことが判明した場合、多くのデータを失わずに、この状況から回復する方法はありますか。
A. データが破損したタイミング、破損したデータ、保有しているバックアップによって、回復方法は異なります。最善策は障害発生時に取るべき行動を明記した障害回復計画を事前に用意しておくことですが、仮定の質問を投げかけることになるので、それ以外の方法について説明しましょう。
ご質問の内容によると、データベースの完全バックアップを実行した後に実行した DBCC CHECKDB コマンドで破損が検出されたとのことですが、データベースをバックアップする前と後のどちらに破損が発生したのかを判断する簡単な方法はありません。データベースのバックアップ前に破損が発生した場合、バックアップには破損したデータベースが含まれているので、データの回復はより複雑になります。
私が当事者であれば、まず、データベースの最新のバックアップを別の場所に復元して、そのデータベースに対して DBCC CHECKDB コマンドを実行します。破損が検出されなければ、次の手順を使用して、データを失うことなくデータベースを復元できます。
- 破損したデータベースのログの末尾のバックアップを使用します (最後に行われたトランザクションをキャプチャします)。
- WITH NORECOVERY オプションを指定して、データベースの最新の完全バックアップを復元します。
- WITH NORECOVERY オプションを指定して、データベースの完全バックアップを作成した後に作成されたすべてのトランザクション ログ バックアップとログの末尾のバックアップを順に復元します。
- RESTORE <データベース名> WITH RECOVERY コマンドを実行して、復元シーケンスを完了します。
最後に、もう一度 DBCC CHECKDB コマンドを実行して、データが破損していないことを確認し、データの破損を引き起こした根本的な原因を分析して、問題を修復する手順を実行します。
上記の復元手順を実行して破損が検出された場合は、トランザクション ログ バックアップのいずれかで何かが破損しているか、メモリ内で何かが破損して、それがトランザクション ログに含められている可能性があります。この場合は、特定の時点への復旧を実行して、破損が発生した時期を特定し、その直前の時点までデータを回復する必要があります。この手順については、このコラムで説明する範囲を超えているので取り上げませんが、詳細については、オンライン ブックを参照してください。
最新のデータベース バックアップに破損したデータが含まれている場合は、上記の手順を実行する際に、最新のバックアップではなく、1 つ前のバックアップを使用しなければならないことがあります。その場合には、1 つ前のデータベースの完全バックアップと、そのバックアップが作成されてから最新のデータベースの完全バックアップが作成されるまでの間に作成されたログ バックアップも必要になります。
もう 1 つの方法は、破損したデータを手動または DBCC CHECKDB の REPAIR オプションを使用して削除して、以前のバックアップからデータの一部を回復することです (この方法は、許容できる最大のダウンタイム (つまり RTO) の制約を満たす形で使用できます)。
破損からの回復は、問題が発生した場所と利用できるオプションによって、非常に簡単な場合もあれば、非常に困難な場合もあります。この問題については、今後数か月に渡って、いくつかのコラムで取り上げたいと思っています。
Q. 開発チームで、SQL Server 2008 の変更の追跡機能を使用するソリューションを構築する予定です。この機能についてのドキュメントを読むと、関連のあるデータベースでスナップショット分離の設定を有効にする必要があると思われるのですが、この設定がパフォーマンスに与える影響を懸念しています。何かご意見をいただけますでしょうか。
A. 変更の追跡機能については、2008 年 11 月号のコラム「企業データベースの変更を追跡する」で取り上げましたが、ご指摘のとおり、行のバージョン管理を有効にする必要があります。変更されたデータを取得する一般的なメカニズムは、次のようになっているので、この設定を有効にする必要があります。
- 変更の追跡システムをクエリして、変更されたテーブルの行を特定します。
- テーブル自体をクエリして、変更された行を取得します。
行のバージョン管理メカニズムなしでは、クエリの実行中に変更の追跡クリーンアップ タスクが実行されると、1 つ目のクエリで正しくない結果が返される可能性があります。また、1 つ目のクエリ結果で参照されているテーブルの行が 2 つ目のクエリを実行する前に削除されると、2 つ目のクエリでエラーになる可能性があります。
変更を追跡しているデータと必要なユーザー テーブルをロックして、動作を安定させることは可能です。ただし、この方法では、ブロックによって同時実行性が低下し、ワークロードのスループットが低下します。動作を安定させるもう 1 つの方法は、スナップショット分離を使用することです。
スナップショット分離には 2 種類あります。1 つはトランザクション レベルで整合性を提供するもの (データベース オプション: allow_snapshot_isolation) で、もう 1 つは T-SQL ステートメント レベルで整合性を提供するもの (データベース オプション: read_committed_snapshot) です。トランザクション レベルのオプションは、変更の追跡機能を適切に使用するために必要で、このオプションを "スナップショット分離" と呼びます。
スナップショット分離では、テーブル レコードのバージョンが維持されるので、明示的なトランザクションが開始すると、そのトランザクションでは、その時点で整合性のあるデータベースのビューを参照できます。変更の追跡機能を使用するには、上記 2 つのクエリは 1 つの明示的なトランザクション内にラップし、分離レベルはスナップショットに設定する必要があります。この組み合わせにより整合性が保証されます。
スナップショット分離については、私の妻 Kimberly が執筆したホワイト ペーパー「SQL Server 2005 の行のバージョン管理に基づいたトランザクション分離 (英語)」で詳しく説明しています。
スナップショット分離にはパフォーマンス上の問題が 2 つあります。1 つは、データベースのテーブルに変更を加える際には、レコードのバージョンを生成する必要があることです (そのバージョンを使用する必要がない場合も同様に生成する必要があります)。変更前のバージョンのレコードは、バージョン ストアにコピーする必要があります。また、別のトランザクションが開始され、現在のトランザクションが完了する前に適切なバージョンのレコードが必要になった場合に備えて、新しいレコードでは、古いレコードへのリンクを保持している必要があります。その結果、すべての更新操作で処理上のオーバーヘッドが発生します。
バージョン ストアは tempdb データベース内にありますが、これが 2 つ目のパフォーマンス上の問題となる可能性があります。tempdb データベーでスは、すべての接続とデータベースで共有されているので、SQL Server インスタンスによっては、最も処理量の多いデータベースになります。一般的に、行のバージョン管理を有効にしていない場合でも、tempdb がパフォーマンスのボトルネックになることがあります。行のバージョン管理を有効にすると、(使用領域と I/O 操作の観点から) tempdb データベースにかかる負荷がさらに高くなり、全体的なワークロード スループットが低下することがあります。
詳細については、ホワイト ペーパー「SQL Server 2005 で tempdb を操作する (英語)」を参照してください。ここで紹介したホワイト ペーパーは、どちらも SQL Server 2005 を対象としていますが、SQL Server 2008 にも適用されます。
Q. DBCC CHECKDB コマンドでは、データベースに含まれるものを完全かつ包括的にチェックできますか。というのも、そうではないという話を聞いたからです。また、DBCC CHECKDB コマンドでは、問題をすべて修復できますか。これについても、できないという話を聞きました。DBCC CHECKDB コマンドが包括的な機能を備えていない場合、代わりにできることはありますか。
A. 「どちらとも言えない」というのがご質問への回答です。DBCC CHECKDB コマンドは、包括的な整合性チェック機能を備えており、そのチェック項目はバージョンごとに増えています。ですが、ご指摘のとおり、DBCC CHECKDB コマンドで検証できないこともいくつかあります。
DBCC CHECKDB コマンドでは、次のことをチェックできます。
- システム カタログの整合性
- 割り当てのメタデータの整合性
- すべてのユーザー テーブルの整合性
DBCC CHECKDB コマンドのチェック項目の詳細について説明するのは、回答の範囲を超えるので説明しませんが、使用されているデータベース ページは、最低でもメモリに読み込まれ検証されます (詳細については、私のブログか『Microsoft SQL Server 2008 Internals』(英語) を参照してください)。DBCC CHECKDB では、I/O サブシステムの問題によって発生する一般的な破損を特定できます (破損の約 99.99% が I/O サブシステムの問題に起因します)。
すべての SQL Server のバージョンでチェックされない既知の項目は、データベースに格納されている列のコンテンツとインデックス キーの統計 (これらについては、今後のリリースで対応する可能性があります) と制約 (テーブル間の外部キーの制約など) の有効性です。制約の有効性は DBCC CHECKDB コマンドとは別に DBCC CHECKCONSTRAINTS コマンドを使用してチェックできます。修復処理では、制約が考慮されず、意図せずに制約が無効になることがあるので、制約を含むデータベースで修復処理を実行するように強制されている場合、制約は修復処理の後に検証することをお勧めします。詳細については、「オンライン ブック」を参照してください。
修復システムでは、すべてのものを修復できるわけではありません。妥当な時間内に適度な精度を確保できない破損もあります。そのような破損は少数なので、私のブログの記事「CHECKDB From Every Angle: Can CHECKDB Repair Everything?」(CHECKDB の詳細: CHECKDB ですべてのものを修復できるか、英語) で説明しています。たとえば、システム カタログの破損ページについて考えてみましょう。この場合、唯一の修復方法はページを削除することです。ですが、ページにデータベースのユーザー テーブルのメタデータが含まれている場合はどうでしょうか。ページを削除すると、ページに含まれるユーザー テーブルも削除されるので、ページを修復することができません。
多くの修復方法は、妥当な時間内に適度な精度を確保する唯一の手段ですが、なんらかのデータの損失が発生します。そのため、修復は、障害回復を実行する際の最終手段と見なす必要があります。データの損失を防ぐ唯一の方法は、包括的なバックアップ戦略で作成したバックアップを使用することです (ただし、なんらかの形でデータベースの同期コピーを持っている場合は除きます)。
DBCC CHECKDB コマンドは、破損を検出するのに十分な機能を備えていますが、データベースのメンテナンス戦略の一環として、このコマンドを定期的に実行して、早い段階で破損を検出する必要があります (詳細については、私のブログ記事「Importance of Running Regular Consistency Checks」(定期的に整合性チェックを実行することの重要性、英語) を参照してください)。DBCC CHECKDB コマンドよりも包括的な処理を行う機能はありませんが、すべてのデータベースでページのチェックサムを有効にすると、DBCC CHECKDB コマンドの効果を高められます。これにより、SQL Server では、SQL Server のメモリ外で何かによってデータベース ページが変更されたことを検出できます。
Q. 圧縮について混乱しています。ある記事ではデータ ファイルを圧縮するのは良いことだと記載されていましたが、別の記事では望ましくないことだとされていました。ログ ファイルの圧縮について調べたときにも同じような状況が発生しました。どちらが正しいのでしょうか。
A. 圧縮は非常に誤解されやすい操作です。データ ファイルの圧縮とログ ファイルの圧縮は、大きな混乱を招く要因となっています。
データ ファイルの圧縮操作では、ファイルの末尾に近いところからファイルの先頭に近いところにデータベースのページを移動します。この操作により、データ ファイルの末尾には、OS に返却できる空き領域が作成されます。つまり、データ ファイルは物理的に小さくなります。
一方、トランザクション ログ ファイルの圧縮操作では、何も移動されません。トランザクション ログのレコードを保持する必要がない限り、ファイルの末尾にあるトランザクション ログの空き領域を削除します。操作が正常に行われると、ログ ファイルは物理的に小さくなります。
混乱は、この 2 つの操作の副作用と、この 2 つの操作を実行すべきタイミングによって生じます。
ユーザーは、空き領域を作成するためにデータ ファイルを圧縮するようにアドバイスを受けます (または、実際に圧縮します)。空き領域を作成するのは、インデックスのメンテナンス ジョブによりデータ ファイルのサイズが大きくなったり、ドライブの空き領域が少なくなった場合が考えられます。このような状況が発生すると、この無駄な領域を空き領域にしたいと考えるのが当然でしょう。ただし、この領域が再度必要になる可能性は高いため、何度もファイルを圧縮して、ファイルのサイズが自動的に大きくなるという状態を繰り返すのではなく、この空き領域をデータ ファイルで利用できるようにするのが望ましいことが往々にしてあります。
データ ファイルの圧縮操作には、好ましくない副作用が伴うため、この操作はなるべく行わないことをお勧めします。データ ファイルを圧縮すると、大規模なインデックスの断片化が発生し、クエリのパフォーマンスに影響することがあります。私のブログ記事「Why You Should Not Shrink Your Data Files」(データ ファイルを圧縮すべきでない理由、英語) では、このことを示す単純なスクリプトを紹介しています。
このブログ記事では、データ ファイルを圧縮するのが適している場合 (めったにありませんが) や断片化という副作用を回避する方法についても説明しています。残念ながら、データ ファイルの圧縮による副作用について言及することなく、この操作を推奨しているケースが散見されます。
ログ ファイルの圧縮は、データ ファイルの圧縮よりも、さらに慎重に実行する必要がある操作です。一般的に、ログ ファイルを圧縮するのは、適切に管理されていないことが原因で、ログ ファイルのサイズがデータ ファイルに対して過度に大きくなっているか、ログ ファイルのサイズが大きくなっていることを認識していて、そのサイズをできるだけ小さくしたいと考えている場合です。アクティブなデータベースのログ ファイルのサイズは適度なサイズに抑える必要がありますが、ログ ファイルは、データベースのアクティビティに対応して圧縮や拡張によってサイズが調整される必要がないように管理する必要があります。
トランザクション ログの詳細については、TechNet Magazine 2009 年 2 月号の記事「SQL Server のログ記録と復旧について」を参照してください。また、トランザクション ログのサイズ管理について取り上げた私のブログ記事「Importance of Proper Transaction Log Size Management」(トランザクション ログのサイズを適切に管理することの重要性、英語) も参照してください。
要するに、圧縮操作は、めったに行うべきものではなく、圧縮操作を実行する際には、その操作による悪影響も十分に理解したうえで実行する必要があります。
Paul S. Randal は SQLskills.com の代表取締役であり、Microsoft Regional Director でもあり、SQL Server MVP でもあります。1999 年から 2007 年までは、マイクロソフトの SQL Server ストレージ エンジン チームに所属していました。また、SQL Server 2005 の DBCC CHECKDB/repair コードを記述し、SQL Server 2008 の開発時にはコア ストレージ エンジンを担当していました。Paul は障害回復、高可用性、およびデータベース メンテナンスの専門家であり、世界中のカンファレンスで定期的に発表を行っています。彼は、SQLskills.com/blogs/paul (英語) でブログを公開しており、Twitter は Twitter.com/PaulRandal (英語) でツイートしています。