トランザクションログ (LDFファイル) 肥大化の原因確認方法 [SQL Server]

SQL Server を使用していると、トランザクションログファイル (.ldf) が肥大化し、ディスクの空き容量を圧迫するという現象を経験するかと思います。

今回は、「トランザクションログファイル (.ldf) が肥大化している原因を特定する方法」について紹介していきたいと思います。

本ブログ内容は、SQL Server 2019 をベースにしていますが、SQL Server 2019 以前のバージョンでも同様の方法で確認可能です。

トランザクションログファイル (.ldf) が肥大化している原因を特定する方法
まとめ
SQL Server トランザクションログ肥大化対策関連ブログ

トランザクションログファイル (.ldf) が肥大化している原因を特定する方法

まず初めに、システムカタログビュー sys.databases に対して以下のクエリを実行して、recovery_model_desc 列 (データベース復旧モデル)、log_reuse_wait_desc 列 (トランザクションログの再利用ができない理由／切り捨てが出来ない理由) を確認します。

select sdb.name, sdb.database_id, recovery_model_desc, log_reuse_wait, log_reuse_wait_desc, smf.name as 'log_logical_name', smf.size*8/1024 as 'size(MB)', smf.physical_name
from sys.databases sdb
left outer join sys.master_files smf on sdb.database_id = smf.database_id
where smf.type = 1
and sdb.database_id > 4

[出力例]

f:id:nobtak:20200201152819p:plain

recovery_model_desc 列 (データベース復旧モデル) が「FULL」の場合は「完全復旧モデル」、「SIMPLE」の場合は「単純復旧モデル」になります。size(MB) 列から肥大化しているトランザクションログファイルの recovery_model_desc 列の値を確認します。

recovery_model_desc 列の詳細は、以下URLを参照

recovery_model_desc 列の一部のステータスを参考までに載せます。説明の部分については、公開情報の内容とは少し変えています。

log_reuse_wait	log_reuse_wait_desc	説明
0	NOTHING	現状、ログの切り捨てが行えている状態
2	LOG_BACKUP	トランザクションログのバックアップを実施することでログレコードの切り捨てが行える状態
4	ACTIVE_TRANSACTION	アクティブなトランザクションが完了することでログレコードの切り捨てが行える状態
5	DATABASE_MIRRORING	データベースミラーリングの同期処理が完了することでログレコードの切り捨てが行える状態
6	レプリケーション	レプリケーションの同期処理が完了することでログレコードの切り捨てが行える状態
9	AVAILABILITY_REPLICA	可用性グループレプリカ間の同期処理が完了することでログレコードの切り捨てが行える状態

「単純復旧モデル(SIMPLE)」の場合

① log_reuse_wait_desc 列が「NOTHING」の場合

一般的に１つのトランザクションの中で大量のDML処理(更新／挿入／削除)が行われたなどの要因により、トランザクションログファイルが肥大化した可能性が疑われます。そのため、１つのトランザクションの中で大量のDML処理を実施しているクエリ、ストアドプロシージャ、ジョブなどが存在しないかを確認するとよいでしょう。この状態の場合、ログレコードの切り捨てが行える状態であるため、肥大化したトランザクションログファイル領域が再利用可能な状態になっています。

トランザクションログを肥大化させているクエリを特定する方法は、以下URLを参照

② log_reuse_wait_desc 列が「ACTIVE_TRANSACTION」の場合

アクティブなトランザクションが残っていることに起因してログレコードの切り捨てが行えない状態であるため、長期間実行中のトランザクションが残っていないかを確認するとよいでしょう。Commit 待ちのトランザクションが残っている場合もこのステータスになります。アクティブなトランザクションが完了した後、ログレコードの切り捨てが行える状態になります。

「ACTIVE_TRANSACTION」の対処方法は、以下URLを参照

「完全復旧モデル(FULL)」の場合

① log_reuse_wait_desc 列が「ACTIVE_TRANSACTION」の場合

単純復旧モデルと同様に、アクティブなトランザクションが残っていることに起因してログレコードの切り捨てが行えない状態であるため、長期間実行中のトランザクションが残っていないかを確認するとよいでしょう。Commit 待ちのトランザクションが残っている場合もこのステータスになります。アクティブなトランザクションが完了した後、ログレコードの切り捨てが行える状態になります。

「ACTIVE_TRANSACTION」の対処方法は、以下URLを参照

② log_reuse_wait_desc 列が「LOG_BACKUP」の場合

トランザクションログのバックアップが行われていないことに起因し、ログレコードの切り捨てが行えない状態であるため、トランザクションログのバックアップを実施することでログの切り捨てが行える状態になります。SQL Server Management Studio (SSMS) からトランザクションログのバックアップを実行することが出来ますが、既存のバックアップ処理の中でトランザクションログのバックアップを採取している場合など、個別にトランザクションログのバックアップを実施した場合、リストア時にも個別に採取したトランザクションログのバックアップファイルが必要になる点を気を付ける必要があります。仮に個別に採取したバックアップファイルを削除した場合、ログチェーン (LSN)が途切れるため、障害直前までのリカバリが出来なくなります。

「LOG_BACKUP」の対処方法は、以下URLを参照

③ log_reuse_wait_desc 列が「DATABASE_MIRRORING」の場合

SQL Server 2019でミラーリングを構築される人は少ないかと思いますが、プリンシパル、ミラー間の同期が正常に行われていないことに起因して、プリンシパル側のログレコードの切り捨てが行えない状態であるため、ミラーリングを正常な状態に復旧させる、もしくは、一旦ミラー側をミラーリング構成から切り離すことでログレコードの切り捨てが行える状態になります。

「DATABASE_MIRRORING」の対処方法は、以下URLを参照

④ log_reuse_wait_desc 列が「レプリケーション」の場合

パブリッシャ、サブスクライバ間の同期が正常に行われていないことに起因し、パブリッシャ側のログレコードの切り捨てが行えない状態であるため、レプリケーションを正常な状態に復旧させることで、ログレコードの切り捨てが行える状態になります。

⑤ log_reuse_wait_desc 列が「AVAILABILITY_REPLICA」の場合

プライマリレプリカ、セカンダリレプリカ間の同期が正常に行われていないことに起因し、プライマリレプリカ側のログレコードの切り捨てが行えない状態であるため、可用性グループのレプリカ間の同期を正常な状態に復旧させる、もしくは、一旦同期のできていないレプリカを可用性グループから切り離すことで、ログレコードの切り捨てが行える状態になります。