NOBTAの気ままにITブログ

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

トランザクションログ肥大化の対処方法 (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」コマンドを実行する方法があります。

 

動的管理ビューから確認

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')」コマンドを実行し、トランザクションログの使用率が下がったことを確認します。

 

「dbcc opentran」コマンドから確認

1) 「dbcc opentran」コマンドを実行します。

dbcc opentran('<データベース名>')

 f:id:nobtak:20200208231031p:plain

2) アクティブなトランザクションが残っている場合、以下のような出力結果が表示されるため、「SPID (サーバー プロセス) : **」の値を確認し、「動的管理ビューから確認」の手順 3) - 6) を同様に実施します。

 

 f:id:nobtak:20200208223701p:plain

 

SQL Server トランザクション ログ肥大化対策 関連ブログ 

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

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

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

4) トランザクションログ肥大化の対処方法 (log_reuse_wait_desc : LOG_BACKUP)

5) エラー 9002、17053 の対処方法

6) トランザクションログ肥大化の対処方法 (log_reuse_wait_desc : DATABASE_MIRRORING)

 

※ 2022年1月 更新