NOBTAの気ままにITブログ

Azure全般 / SQL Serverに関する情報を発信していきます。


スポンサーリンク

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


スポンサーリンク

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

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

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

 

 まず初めに、システム カタログ ビュー 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 列の詳細は、

トランザクション ログ (SQL Server) - SQL Server | Microsoft Docs を参照

 

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」の場合

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

 

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

トランザクションログを肥大化させているクエリの特定方法 [SQL Server] - NOBTAの気ままにITブログ

を参照

 

② log_reuse_wait_desc 列が「ACTIVE_TRANSACTION」の場合

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

 

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

 ① log_reuse_wait_desc 列が「ACTIVE_TRANSACTION」の場合

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

 

② log_reuse_wait_desc 列が「LOG_BACKUP」の場合

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

 

③ log_reuse_wait_desc 列が「DATABASE_MIRRORING」の場合

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

 

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

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

 

 ⑤ log_reuse_wait_desc 列が「AVAILABILITY_REPLICA」の場合

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

 

今回は、トランザクションログ (LDFファイル) 肥大化の原因確認方法について紹介しましたが、log_reuse_wait_desc 列の各ステータス単位での復旧方法について紹介していきたいと思います。

 


スポンサーリンク