トランザクション ログ (LDFファイル) が肥大化した場合、ディスクの空き容量が枯渇するなど、肥大化したトランザクション ログ を圧縮し、ディスクの空き容量を確保したいという状況があるかと思います。
トランザクション ログ (LDFファイル) の肥大化を解消する方法については、以下の URL を参照
トランザクション ログ を圧縮する際、意図したサイズまでトランザクションログ を圧縮することができない場合があります。
今回は、トランザクションログの物理アーキテクチャ : 仮想ログファイル (VLF : Virtual Log File) について、自分の整理も兼ねてまとめてみようと思います。
※ 第18回では「SQL Server のライフサイクル ポリシー」についてまとめています。
仮想ログファイルのアーキテクチャについて
トランザクション ログファイル (LDF) は内部的に仮想ログファイル (VLF : Virtual Log File) という論理的な単位で分割されています。
仮想ログファイルのサイズは、トランザクション ログファイルの初期作成時、拡張時にSQL Server エンジンにより動的に決定されます。
仮想ログファイル (VLF : Virtual Log File) は、先頭からログレコードの書き込みが行われ、先頭の仮想ログファイルが一杯になれば、次の仮想ログファイルにログレコードの書き込みが行われます。
また、トランザクションログの切り捨ては仮想ログファイル単位で行われます。
例えば、仮想ログファイルが8個あり、先頭から4個目の仮想ログファイルがアクティブな状態でログレコードの書き込みが行われているとします。
上記の状態でトランザクションログの切り捨てが行われた場合、先頭から3個目までの仮想ログファイルが解放され再利用可能な状態になり、先頭から4個目の仮想ログファイルはアクティブのまま残ります。
その後、先頭から4個目の仮想ログファイルが一杯 (もしくは チェックポイント処理を実行) になった場合、先頭の仮想ログが再利用可能になっているため、先頭の仮想ログファイルがアクティブとなりログレコードの書き込みが行われていきます。
そして、先頭から4個目の仮想ログファイルはアクティブな状態ではなくなったため、次回 トランザクションログの切り捨てが行われたタイミングで再利用な状態になります。
上記のように仮想ログファイルは循環的に利用されますが、以下のような状況の場合、トランザクションログの切り捨てが行われる前に大量のログレコードが仮想ログファイルに書き込み続けられることで、トランザクションログのファイルサイズ (仮想ログファイル数) が肥大化する可能性があります。
- ロングトランザクションの中で大量のDML処理(UPDATE/INSERT/DELETE)が実行される。
- トランザクション ログのバックアップが実行される間隔内で大量のDML処理(UPDATE/INSERT/DELETE)が実行される。
仮想ログファイルのファイル数、サイズについて
仮想ログファイル数、サイズは、トランザクションログの初期作成時、拡張時にSQL Serverエンジンにより動的に決定されます。
仮想ログファイル数は、基本的に以下のルールによって決定されるようです。
| 初期作成／拡張サイズ | 仮想ログファイル数 |
| 64MB未満の場合 | 4 |
| 64MB以上1GB未満の場合 | 8 |
| 1GB以上 | 16 |
※ 拡張サイズが現在のトランザクションログサイズの 1/8 未満の場合、仮想ログファイル数は 1 となる。
※ SQL Server 2022 の場合、拡張サイズが64MB未満では仮想ログファイル数は 1 となる。
また、仮想ログファイルサイズは、初期作成／拡張されたサイズを仮想ログファイル数で割ったサイズになっているようです。
例えば、トランザクションログの初期サイズ 10GB (1024MB) のデータベース (今回は「DB1」) を作成した場合、
仮想ログファイル数 確認コマンド (例)
| select DB_NAME(database_id) as 'database_name',* from sys.dm_db_log_info(DB_ID('DB1')) go |
仮想ログファイル数 確認コマンド実行結果 (例)
仮想ログファイルが 16個 作成され、各仮想ログファイルサイズ (vlf_size_mb) は 639.93MB になっていることが確認できます。
トランザクションログの圧縮について
トランザクションログの圧縮では、トランザクションログファイルのオフセットの末尾から連続した再利用可能な仮想ログファイルサイズが解放されます。
以下の例の場合、2番目から16番目までの仮想ログファイルが再利用可能な状態になっています。
仮想ログファイル数 (例)
上記の状態で、トランザクションログを圧縮するコマンドを実行してみます。
トランザクションログ圧縮コマンド (例)
※ 本例では、10240 MBで作成したトランザクションログファイルを可能な限り 64MB まで圧縮するコマンドを実行。
| DBCC SHRINKFILE (N'DB1_log' , 64) go |
結果、以下のように仮想ログファイルが2個残った状態になりました。
トランザクションログ圧縮後の仮想ログファイル数 (例)
仮想ログファイルは、最低でも 2個 保持される動作になっているようです。
10240 MBで作成したトランザクション ログファイルを可能な限り 64MB まで圧縮したかったのですが、結果として 約 1 GB までしか圧縮することができませんでした。
なお、今回のデータベースの場合、トランザクション ログファイルの初期サイズを 10240 MB で作成したため、各仮想ログファイルサイズが 約 639 MBとなっています。
また、最低でも2個の 仮想ログファイルを保持する動作になっているため、今回のデータベースの場合、仮想ログファイルサイズ＊2 = 約 1GB 以下のサイズに圧縮することができないということになります。
まとめ
今回は、仮想ログファイルのアーキテクチャとトランザクションログを圧縮する際の注意点についてまとめてみました。
トランザクションログの初期サイズを大きなサイズ (数百テラバイトなど) で作成した場合、一つ当たりの仮想ログファイル (VLF : Virtual Log File) が大きくなってしまいます。
また、トランザクション ログファイルサイズは、最低でも二つの仮想ログファイルサイズよりも小さいサイズに縮小することができないため、トランザクションログの初期サイズはあまり大きなサイズに指定せず、必要に応じて後からトランザクション ログファイルサイズを拡張することを検討してみても良いかと思います。
【第20回】基本から始める SQL Server【トランザクションログの圧縮方法について】へ
※ 2022年10月 現在
