トランザクションログ肥大化の対処方法 (log_reuse_wait_desc : ACTIVE_TRANSACTION) [SQL Server]

SQL Server トランザクションログ (.ldf) の肥大化は様々な要因により発生しますが、今回は、トランザクションログファイル (.ldf) 肥大化の要因が、「log_reuse_wait_desc : ACTIVE_TRANSACTION」の場合の対処方法について紹介していきたいと思います。

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

log_reuse_wait_desc : 「ACTIVE_TRANSACTION」は、アクティブなトランザクション (コミット、ロールバックが行われていない状態のトランザクション) が存在していることに起因して、ログレコードの切り捨てが行えない状態になっていることを示しています。

データベース復旧モデル「単純 (SIMPLE)」､「完全 (FULL)｣共に、このステータスによって、トランザクションログファイルサイズが肥大化する可能性があります。

f:id:nobtak:20200208173110p:plain

f:id:nobtak:20200208173134p:plain

アクティブなトランザクションを確認する方法として、動的管理ビュー「sys.dm_tran_session_transactions」、「sys.dm_tran_database_transactions」を確認する方法、「dbcc opentran」コマンドを実行する方法があります。

動的管理ビューから確認
「dbcc opentran」コマンドから確認
SQL Server トランザクションログ肥大化対策関連ブログ

動的管理ビューから確認

1) 以下のクエリを実行します。

select dts.session_id, dts.transaction_id,dtd.database_transaction_begin_time,
dtd.database_id,db_name(dtd.database_id) as 'Database Name', dtd.database_transaction_log_bytes_used
from sys.dm_tran_session_transactions dts
left outer join sys.dm_tran_database_transactions dtd on dts.transaction_id = dtd.transaction_id
order by dtd.database_transaction_begin_time asc
go

2) 実行結果より、「database_transaction_begin_time」(トランザクションの開始時刻)が最も早い「session_id」(クエリが実行されているセッションID情報) を確認します。

※ 今回の例の場合は、トランザクションの開始時刻が最も早いセッションIDは「59」になります。

f:id:nobtak:20200208183620p:plain

3) トランザクションの開始時刻が最も早いセッションIDで開始されたトランザクションのセッション情報を確認します。

※ 動的管理ビュー「sys.dm_exec_sessions」などでセッションの詳細情報を確認できます。

select session_id, login_time, login_name, host_process_id, host_name, client_interface_name, program_name
from sys.dm_exec_sessions
where session_id = '59'

f:id:nobtak:20200208212727p:plain

※ 今回の場合、SSMSから実行されたクエリのトランザクションが、アクティブな状態で残っていることが確認できました。

4) 該当のセッションで実行されているトランザクションをコミット、ロールバック可能な場合は、コミット、ロールバックを実施、コミット、ロールバックを実施できない場合は、以下の何れかを選択し、アクティブなトランザクションを完了させます。

A. クエリを実行しているアプリケーションを停止する。

※ アクティブな状態で残っているトランザクションは、ロールバックされます。

B. 2) で確認できたセッションを、KILL コマンドで強制終了する。

※ アクティブな状態で残っているトランザクションは、ロールバックされます。

KILL <セッションID>

f:id:nobtak:20200208223233p:plain

5) 1) のクエリを実行し、アクティブなトランザクションが存在しないことを確認します。

※ アクティブなトランザクションが残っている場合、4)の手順を同様に実施します。

6) 「dbcc sqlperf('logspace')」コマンドを実行し、「Log Space Used (%)」(ログ使用率) を確認し、トランザクションログの使用率が下がっていることを確認します。

dbcc sqlperf('logspace')

f:id:nobtak:20200208224739p:plain

※ データベース復旧モデルが「完全 (FULL)」の場合は、log_reuse_wait_desc の状態が「LOG_BACKUP」などに代わり、トランザクションログのバックアップを採取しなければ、アクティブなログレコードが解放されません。そのため、トランザクションログのバックアップを実施後、再度「dbcc sqlperf('logspace')」コマンドを実行し、トランザクションログの使用率が下がったことを確認します。