第19回では、仮想ログファイルのアーキテクチャとトランザクションログを圧縮する際の注意点についてまとめてみました。
今回は、SQL Server トランザクション ログファイルの圧縮方法について、自分の整理も兼ねてまとめてみようと思います。
[前提]
- データベースの復旧モデルが「完全」であることを想定しています。
※ データベースの復旧モデルが「単純」の場合、トランザクションが完了後、自動的に仮想ログファイル内のログ情報の切り捨てが行われ、未使用の状態になります。
トランザクション ログファイルを圧縮するためには
トランザクション ログファイル (LDF) の圧縮は、トランザクション ログファイルの末尾のオフセットから連続した未使用の仮想ログファイルの領域が解放されることになります。
例えば、以下の状態の圧縮前のトランザクション ログファイルがあったとします。
このトランザクション ログファイルを圧縮すると、トランザクション ログファイルの末尾のオフセットから「仮想ログファイル4」と「仮想ログファイル3」が未使用となっているため、「仮想ログファイル4」と「仮想ログファイル3」の領域が解放されます。
それでは、以下の場合はどうでしょう？
このトランザクション ログファイルを圧縮すると、「仮想ログファイル4」、「仮想ログファイル2」、「仮想ログファイル1」が未使用となっていますが、トランザクション ログファイルの末尾のオフセットから連続した未使用領域は「仮想ログファイル4」のみであるため、「仮想ログファイル4」の領域のみが解放されます。
それでは、未使用となっている「仮想ログファイル1」、「仮想ログファイル2」を解放したい場合は、どうすればよいでしょうか？
上記状態では、「仮想ログファイル3」 が Current (現在利用されている) の仮想ログファイルとなります。
仮想ログファイルは循環的に利用されるため、「仮想ログファイル3」に保持可能なログ情報が一杯になる、もしくは Checkpoint (チェックポイント) を実施することで、Currentの仮想ログファイルがトランザクション ログファイルの先頭のオフセットから可能な限り近い仮想ログファイルに移動されることになります。
しかしながら、上記の状態の場合、「仮想ログファイル3」にはログ情報が保持されており未使用状態になっていないため、「仮想ログファイル3」、「仮想ログファイル2」を解放することができません。
そのため、「仮想ログファイル3」を未使用状態にするため、トランザクションログのバックアップを実施します。
トランザクション ログバックアップ後、「仮想ログファイル3」に保持されていたログ情報が解放され、未使用な状態になりました。
このトランザクション ログファイルを圧縮すると、トランザクション ログファイルの末尾のオフセットから「仮想ログファイル3」と「仮想ログファイル2」が未使用となっているため、「仮想ログファイル3」と「仮想ログファイル2」の領域が解放されます。
なお、トランザクション ログバックアップを実施したとしても、トランザクション ログファイルのオフセットの末尾の仮想ログファイルが未使用な状態にならない場合がああります。
その場合には、システム カタログ ビュー sys.databases の log_reuse_wait_desc 列 (トランザクションログの再利用ができない理由／切り捨てが出来ない理由) を確認してみると良いかと思います。
トランザクション ログファイルの末尾の仮想ログファイルが未使用にならない場合の原因を確認したい場合は、以下の URL を参照ください。
トランザクション ログファイル圧縮方法
実際にトランザクション ログファイルを圧縮する方法についてまとめてみたいと思います。
[実行するコマンド]
| use <データベース名> go checkpoint go backup log <データベース名> to disk = '<バックアップ作成パス>' go dbcc shrinkfile (<トランザクションログ論理名>,<圧縮後のサイズ (MB)>) go |
1) 以下のコマンドを実行し、トランザクション ログファイルを圧縮したいデータベース (今回は「db1」) の仮想ログファイルの状況を確認します。
| select DB_NAME(database_id) as 'database_name',* from sys.dm_db_log_info(DB_ID('DB1')) go |
今回の例 (「db1」)の場合、トランザクション ログファイルの末尾のオフセットの仮想ログファイル (8行目) から先頭の仮想ログファイルまで「vlf_active」が ”1” とすべて利用中の状態であることが確認できます。
2) 以下のコマンドを実行し、トランザクション ログファイルのバックアップを実施します。 (今回は「db1」)
| use db1 go checkpoint go backup log db1 to disk = 'c:\temp\db1.log' go |
※ 「checkpoint」コマンドは必須ではないのですが、仮想ログファイルを循環させ Current の仮想ログファイルをトランザクションログファイルのオフセットの先頭に明示的に移動させるために実行しています。
今回の例 (「db1」)の場合、トランザクション ログファイルの末尾のオフセットの仮想ログファイル (8行目) から先頭の仮想ログファイルまで「vlf_active」が ”0” とすべて未使用の状態であることが確認できます。
3) 以下のコマンドを実行し、トランザクション ログファイルを圧縮します。 (今回は「db1」)
| dbcc shrinkfile (db1_log, 10) go |
※ dbcc shrinkfile に指定するトランザクションログ論理名は、システム カタログ sys.sysfiles の「name」列などから確認可能です。
※ dbcc shrinkfile に指定するサイズは MB単位で指定します。 上記の例では「10」MBが指定されており、可能な限り 10MB に近づくようにトランザクションログファイルを圧縮する操作が行われます。
| use <データベース名> go select * from sys.sysfiles go |
今回の例 (「db1」)の場合、可能な限り 10MB に近づくようにトランザクションログファイルを圧縮する操作が行われたため、トランザクションログファイルのオフセットの先頭から10MBに存在する仮想ログファイル (今回は「仮想ログファイル5」) を含むサイズまで圧縮されました。
つまり、トランザクションログファイルのオフセットの先頭から10MBに存在する「仮想ログファイル5」のサイズ（vlf_size_mb) が 16MB となっているため、「仮想ログファイル1」から「仮想ログファイル5」までの合計サイズが 24MB となり、トランザクションログファイルの圧縮後のサイズは 24MB になりました。
※ 本例の場合、dbcc shrinkfile に指定するサイズを 7MB に指定した場合は、トランザクションログファイルのオフセットの先頭から7MBに存在する仮想ログファイルが「仮想ログファイル4」となるため、トランザクションログファイルの圧縮後のサイズは 8MB になることが想定されます。
まとめ
今回は、SQL Server トランザクション ログファイルの圧縮方法についてまとめてみました。
データベースの復旧モデルが「完全」である場合、一般的には トランザクションログのバックアップを実施することでアクティブなログの切り捨てが行われ、仮想ログファイルが未使用な状態になります。
トランザクションログのバックアップを実施したとしてもアクティブなログの切り捨てが行われず、仮想ログファイルが未使用な状態にならない場合は、システム カタログ ビュー sys.databases の log_reuse_wait_desc 列 (トランザクションログの再利用ができない理由／切り捨てが出来ない理由) を確認してみると良いかと思います。
※ 2022年11月現在
