ログ管理の中でお話頂いた [復旧モデル] について振り返ってみます。
今回は復旧モデルによる、ログの使用状況の違いをまとめて見たいと思います。
SQL Server には復旧モデルという考えがあり、この復旧モデルがリストア時に復旧可能なタイミングとトランザクションログの管理 (解放のタイミング) に影響します。
復旧モデルには、以下の 3 種類があります。
- 完全
- 一括ログ
- 単純
完全復旧モデルと一括ログ復旧モデルに関しては、定期的なバックアップによるログの管理が必要となり、単純復旧モデルに関しては特定のタイミングで自動的に解放 (チェックポイント時のログの切り捨て) が行われます。
そのため、完全 / 一括ログ復旧モデルでないとログを使用したリストアを実施することはできません。
まずは、完全復旧モデルと単純復旧モデルのログの利用のされ方について確認してみたいと思います。
今回は、ログファイルを 30MB にして自動拡張を無効にした状態で大量のデータを追加 (INSERT) してテストをしています。
■チェックポイント発生時のログの切り捨て
完全復旧モデルの場合には、チェックポイントが発生してもログは切り捨てられません。そのため、トランザクションログの使用状況が上限に達した際には、トランザクションログが上限に達したというメッセージが表示されそれ以上のデータの追加ができなくなります。
# 一括ログ復旧モデルもチェックポイント時のログ切り捨ては行われないので、同じトレンドになります。
メッセージとしては以下のエラーが出力されます。
|
メッセージ 9002、レベル 17、状態 2、行 2 |
[sys.databases] を確認すると以下の情報を取得することができます。 
今回の場合、ログの領域が再利用できない理由はログ領域がフルになってしまい、バックアップの必要があるという事を確認できます。
それでは、単純復旧モデルにした場合にはログの使用状況はどうなるでしょう。 
データの追加操作としては全く同じ内容を実行したのですが、単純復旧モデルの場合はチェックポイントの発生タイミングに合わせてログの使用状況が下がっていることが確認できます。
この挙動を [チェックポイント発生時のログの切り捨て] と呼びます。
復旧モデルが単純復旧モデルとなっているデータベースはログのバックアップを取得することができません。
そのため、チェックポイントが発生してメモリ上のダーティーページをディスクに書き込んだタイミングでそれまでのログレコードが不要 (変更箇所がデータファイルに書き込まれているため) となり切り捨てることが可能になります。
チェックポイントの発生ですが、バックアップの取得 / 手動による [CHECKPOINT] ステートメントの実行 / [復旧間隔] の設定に応じた自動チェックポイントなどで発生がします。
今回発生しているチェックポイントは自動チェックポイントにより実行されています。
# 最後の一回だけは私が手動でチェックポイントを発生させているので山の形が少し変わっています。
余談ではありますが自動チェックポイントはトレースフラグ [3505] を設定することで無効にすることもできます。
INF: Use Trace Flag 3505 to Control SQL Server Checkpoint Behavior
■完全復旧モデルと一括ログ復旧モデルのログの使用状況の違い
それでは、次に完全復旧モデルと一括ログ復旧モデルの違いを確認してみたいと思います。
この復旧モデルの違いですが、最小ログ記録が可能な操作をした際のログの書き込みが変わってきます。
完全復旧モデルでは、すべての操作のログが完全に記録されますが、一括ログ復旧モデルでは最初ログ記録が可能な操作をした場合は、ログは最小限のもののみ記録がされます。
最小ログ記録が可能な操作
# インデックス系の操作は一括ログ復旧モデルでないと、最小ログにならないと思っていたのですが上記の内容を読むとそうでもないらしいですね。別の機会で検証したいと思います。また、操作方法によっては完全復旧にしていても最小ログになってしまったので今回は完全復旧モデルでは意図的に最小ログ記録にならない操作をしています。
今回は、[BULK INSERT] を使って違いを確認してみたいと思います。 
どちらの復旧モデルでも波形は一緒になってしまっていますね。
これですが、今回は以下のようなクエリを実行していました。
| BULK INSERT Table_1 FROM ‘F:Dataexport.txt’ |
BULK INSERT を実行する場合、上記のクエリでは最小ログ記録での動作にはなりません。
そのため、一括ログ復旧モデルでも完全なログの記憶動作となり、両復旧モデルで同じ波形となっています。
BULK INSERT に限った話ではないのですが、一括インポート時のログを最小ログ記録にするためには条件があり、[テーブルロックを指定] する必要があります。
一括インポートで最小ログ記録を行うための前提条件
# 他にも条件はあるのですが、今回はインデックスを持たない 1 列の単純なヒープ構造のテーブルを使用しています。
それでは、クエリを以下のように書き換えて実行をしてみます。
| BULK INSERT Table_1 FROM ‘F:Dataexport.txt’ WITH (TABLOCK) |
波形だけをみると一緒の波形になっているのですが、ログファイルの使用状況には大きな差が出ています。
テーブルロックを使用する前はログファイルの使用状況は 30MB まで上昇していました。
テーブルロックを使用した場合は、20KB 程度の使用で処理を完了することができています。
# 実は、テーブルロックを使用しなかった場合は、ログがフルになっていたりもしたのですが。
それでは、実際のログレコードから完全ログ記録と最小ログ記録の違いを比較してみたいと思います。
[完全ログ記録] 
[最小ログ記録] 
完全ログ記録の場合は [LOP_INSERT_ROWS] という形で、追加した行単位でログレコードを書き込んでいきます。
最小ログ記録の場合は、[LOP_SET_BITS] という形で行を追加するにあたって [変更のあったエクステント] の情報のみを書き込んでいます。
最小ログ記録が発生する、一括ログ操作に関しての変更は BCM (一括変更マップ : Bulk Changed Map) という領域を使用して管理されるためこのような動きになります。
使用する復旧モデルによって、ログの使用状況に差が出てきます。
単純復旧モデルを使用した場合はチェックポイント発生時に解放できるログは解放されますが、それ以外の復旧モデルに関してはログのバックアップを取得ないと使用している領域は解放されません。
ログはログのバックアップのタイミングを考慮したうえで、そのタイミングで解放される領域で処理がうまく回るようなサイズを指定しておくことで、ログがフルにならず処理を継続することが可能となります。
ただし、一時的なトランザクションの増加に備えて、[自動拡張] を設定して保険とすることも考えられれます。
セミナーの中では自動拡張時のログの書き込み待ちについても触れられていました。
次の投稿では [自動拡張] についてお話しいただいたことについて振り返ってみたいと思います。
