2021年4月26日月曜日

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


オフィス狛 技術部の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;


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

0 件のコメント:

コメントを投稿