SQL Serverのトランザクションログについて（記録される内容、領域の解放、切り捨て）

• facebook

オフィス狛 技術部のJoeです。

先日、担当していたシステムで、SQL Serverのトランザクションログが肥大化し、
ストレージが空き容量不足になる事象が発生しました。
その対応にあたり、SQL Serverのトランザクションログについて
調査する機会がありましたので、簡単ですがまとめてみました。

※下記の内容はSQL Server 2017で確認しています

■SQL Serverのトランザクションログには何が記録されるのか？

SQL Serverはデータベースに設定されている、復旧モデルによって異なります。

※復旧モデルは下記で確認できます。

SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'model';

・単純モデル
トランザクション開始からコミットまでが、トランザクションログに記録されます。

・完全モデル
全ての操作（DDL、DML）がトランザクションログに記録されます。
インデックスの操作など、大量のデータが読み込れると、大量のログが出力されます。

・一括ログモデル
完全モデルと似ていますが、一括操作（bcp、BULK INSERT など）の場合に、使用されるログ領域を縮小します。
インデックス操作（CREATE INDEX）も、最小ログ記録の対象（※）になるようです。
※下記の「最小ログ記録が可能な操作」を参照ください
トランザクション ログ (SQL Server)

■トランザクションログの領域は、いつ解放されるのか？領域を使い切るとどうなるのか？

こちらも「復旧モデル」によって異なります。

・単純モデル
チェックポイント（トランザクションログファイルに書き込み完了のタイミング）で解放されます。

トランザクションログは既存の領域を自動的に再利用しますので、ログ領域の管理は基本的に不要です。
領域を再利用するので、トランザクションログをバックアップすることはできません。

・完全モデル、一括ログモデル
トランザクションログのバックアップで解放されます。

トランザクションログファイルは領域を使い切ると、自動拡張の設定をしていれば、
トランザクションログファイルの上限サイズ（※）まで、物理サイズを自動拡張します。
（もちろんストレージの空きが無ければ拡張できません）
※最大、2TBまで設定できます

トランザクションログファイルは一度拡張されると、
自動縮小のオプションを有効にしていない限り、
自動で物理サイズは縮小されませんので、ストレージは解放されません。

ストレージを解放するには、下記でトランザクションログファイルを縮小し、
物理ファイルサイズを減らすことで、ストレージの空き容量を増やすことができます。

use [データベース名];

-- トランザクションログ論理名を確認
SELECT name FROM sys.database_files where type = 1;

-- トランザクションログファイルを縮小
DBCC SHRINKFILE( [トランザクションログ論理名] , [縮小後の容量(MB)]);

※縮小後の容量の単位はMB（100を指定すると100MB）なので、ご注意ください

■トランザクションログを、手動で切り捨てる方法

ストレージの容量不足や、バックアップを取得する時間が無い場合などに、
手動で切り捨てることができます。

※トランザクションログの使用状況は下記コマンドで確認できます。

DBCC SQLPERF('LOGSPACE');

【方法①】トランザクションログの削除
下記で削除できます。
注意点としては、削除前に一度、データベースの完全バックアップを取得している必要があります。
（取得してい場合はエラーになります）

BACKUP LOG [データベース名] TO DISK = N'NUL';

【方法②】データベースの復旧モデルを「単純モデル」に変更する
下記で変更できます。
注意点ですが、例えば完全モデルから単純モデルに変更する場合、
完全モデルのみで使用できる機能を使用していると、単純モデルへ変更できないようです。
※以前、AWS RDSで、マルチAZ「あり (ミラーリング)」が設定されている完全モデルのデータベースは、単純モデルに変更することができませんでした

ALTER DATABASE [データベース名] SET RECOVERY SIMPLE;

以上です。
トランザクションログで何か問題があった際に、お役に立てば幸いです。

SQL Server