SQL Server: 単純復旧モデルでログが肥大し続ける現象を調査した話

背景

単純復旧モデルのDBにて、トランザクションログが肥大し続けてドライブ容量が逼迫する事象が発生しました。
単純復旧モデルは更新が完了するとログが切り捨てられるため、完全復旧モデルと比較するとログの肥大は起きにくい復旧モデルです。
原因の調査と解決までの流れをご紹介したいと思います。

調査

1. なぜログの切り捨てが行われないかを確認

select * from sys.databases

sys.databasesのlog_reuse_wait_descを確認することで、ログが切り捨てられない原因が分かります。
今回は「ACTIVE_TRANSACTION」となっていました。
これは、名前の通り、アクティブなトランザクションが存在し続けていることが原因であることを指しています。
この時点では、どのクエリが具体的な原因かまではわかりません。

2. どのクエリが原因かを確認

dbcc opentran

dbcc opentraを実行することで、トランザクション ログ内に存在する最も古いアクティブなトランザクションの情報が取得できます。

SPID=160というセッションIDを取得できました。

3. セッション情報を確認

select 'kill ' + cast(session_id as varchar) as command, session_id, status, cpu_time, memory_usage, reads, logical_reads, writes, last_request_start_time, last_request_end_time, 'dbcc inputbuffer(' + cast(session_id as varchar) + ')' as showquery_command, client_interface_name, program_name
from sys.dm_exec_sessions
where session_id in (160)

1:該当のセッションはstatus=sleepingで現在は実行されていないようでした。
2:該当セッションのリソース使用状況（CPU等）は、何度上記クエリを実行しても変化がありませんでした。

以上の結果を踏まえて、該当セッションをKILLしました。

4. 状況確認

KILL完了後、dbcc opentranしてもアクティブなトランザクションは無くなりました。

また、トランザクションログも切り捨てられ、容量の逼迫が解消されました。

まとめ

単純復旧モデルでログが肥大した際の、調査の流れと解消方法の一例をご紹介しました。
まずはsys.databasesの「log_reuse_wait_desc」を確認するところから始めて、その後の調査方針を決めるのが良さそうです。