RDS for SQL Serverでトランザクションログの使用率を確認する方法

この文書の適用条件

そのまま実行できるコマンド

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: VIEW SERVER STATE
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
DBCC SQLPERF(LOGSPACE);

-- 対象: SQL Server 2012 以降 / Amazon RDS for SQL Server
-- 権限: VIEW SERVER STATE
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
USE [SampleDB];
GO
SELECT
    DB_NAME(database_id)                                AS database_name,
    total_log_size_in_bytes / 1024 / 1024               AS total_log_size_mb,
    used_log_space_in_bytes / 1024 / 1024               AS used_log_space_mb,
    used_log_space_in_percent                           AS used_log_space_pct,
    log_space_in_bytes_since_last_backup / 1024 / 1024  AS log_since_last_backup_mb
FROM sys.dm_db_log_space_usage;

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: sys.databases のメタデータ可視性
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT
    d.name                  AS database_name,
    d.state_desc            AS database_state,
    d.recovery_model_desc   AS recovery_model,
    d.log_reuse_wait        AS log_reuse_wait_id,
    d.log_reuse_wait_desc   AS log_reuse_wait_desc,
    d.is_cdc_enabled        AS is_cdc_enabled,
    d.is_published          AS is_published,
    d.is_subscribed         AS is_subscribed
FROM sys.databases AS d
WHERE d.database_id > 4        -- システムデータベースを除外
ORDER BY d.name;

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: 対象DBへの接続権限
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
USE [SampleDB];
GO
SELECT
    DB_NAME()                                   AS database_name,
    f.file_id,
    f.name                                      AS logical_name,
    f.type_desc,
    f.size * 8 / 1024                           AS current_size_mb,
    CASE
        WHEN f.max_size IN (-1, 268435456) THEN NULL   -- 無制限扱い
        ELSE f.max_size * 8 / 1024
    END                                         AS max_size_mb,
    CASE
        WHEN f.is_percent_growth = 1 THEN CAST(f.growth AS varchar(10)) + ' %'
        ELSE CAST(f.growth * 8 / 1024 AS varchar(10)) + ' MB'
    END                                         AS autogrowth,
    (f.size - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS bigint)) * 8 / 1024 AS free_space_mb
FROM sys.database_files AS f
WHERE f.type_desc = 'LOG';

結果の読み方

こういう状況で使います

• ログファイルだけが増え続け、ストレージの空きが減っていく
• エラー 9002「データベースのトランザクション ログがいっぱいです」で書き込みが失敗する
• ログを縮小してもすぐに元のサイズまで戻る
• Amazon RDS で自動バックアップを設定しているのに、ログ使用率が下がらない

考えられる原因（可能性の高い順）

1. 01

   復旧モデルが FULL なのにログバックアップが行われていない（LOG_BACKUP）

   完全復旧モデルでは、ログバックアップを取るまでログ領域が再利用されません。復旧モデルを FULL にしただけでログバックアップの運用が無い状態が、この症状の最も一般的な原因です。

2. 02

   長時間開いたままのトランザクションがある（ACTIVE_TRANSACTION）

   最も古い未コミットトランザクションより後ろのログは切り捨てできません。アプリケーションが `BEGIN TRAN` のまま放置している、バッチが異常終了してロールバック中、といったケースが該当します。

3. 03

   レプリケーションまたは CDC が未読のログを保持している（REPLICATION）

   トランザクションレプリケーションのログリーダーエージェント、または CDC のキャプチャジョブが停止していると、まだ読み取っていないログが解放されずに残ります。

4. 04

   可用性グループ／ミラーリングの同期遅延（AVAILABILITY_REPLICA / DATABASE_MIRRORING）

   セカンダリへ送信済みかつ適用済みになるまでログは保持されます。セカンダリの停止やネットワーク遅延でログが溜まります。

5. 05

   長時間のバックアップ・復元が実行中（ACTIVE_BACKUP_OR_RESTORE）

   大容量データベースのバックアップ中はログが切り捨てられません。バックアップ完了後に解消するかを確認します。

6. 06

   単一トランザクションでの大量更新

   数千万行の一括削除・更新を1トランザクションで実行すると、コミットするまでその分のログが必要です。ログサイズはトランザクション単位の設計に依存します。

確認手順

1. 1

   全データベースのログ使用率を取得する

   参照のみ

   `DBCC SQLPERF(LOGSPACE)` を実行し、使用率の高いデータベースを特定します。

2. 2

   `log_reuse_wait_desc` を確認する

   参照のみ

   `sys.databases` を参照し、値が NOTHING 以外で継続しているかを見ます。ここで原因の分類がほぼ決まります。

3. 3

   復旧モデルとログバックアップの有無を突き合わせる

   参照のみ

   `recovery_model_desc` が FULL の場合、`msdb.dbo.backupset` を `type = 'L'`（ログバックアップ）で絞り、直近のログバックアップ時刻を確認します。

4. 4

   開いたままのトランザクションを探す

   参照のみ

   `log_reuse_wait_desc = ACTIVE_TRANSACTION` の場合は、`sys.dm_tran_active_transactions` などで最古のトランザクションを特定します。

5. 5

   CDC・レプリケーションの状態を確認する

   参照のみ

   `log_reuse_wait_desc = REPLICATION` の場合は、CDC キャプチャジョブとログリーダーエージェントの稼働状態を確認します。

6. 6

   数分間隔で再取得して推移を見る

   参照のみ

   一時的な CHECKPOINT や LOG_SCAN であれば、次回取得時には NOTHING に戻ります。1回の値だけで判断しないでください。

対応方法

バージョン・環境による違い

これで解決しない場合に確認すること

• 最古のトランザクションの開始時刻

  `ACTIVE_TRANSACTION` が続く場合、どのセッションがいつからトランザクションを開いているかを特定します。

• CDC キャプチャジョブとログリーダーの稼働状態

  `REPLICATION` が続く場合、キャプチャ側が止まっていないか、配布データベース側で滞留していないかを確認します。

• 仮想ログファイル(VLF)の数

  `DBCC LOGINFO`（バージョンにより `sys.dm_db_log_info`）で VLF 数を確認します。数が極端に多い場合、復旧やログ処理が遅くなります。

• ストレージ側の空き容量とIOPS

  ログ拡張が失敗している場合、原因がデータベースではなくストレージ側であることがあります。

この文書の根拠と限界

よくある質問

この文書がカバーする質問

• SQL Serverのトランザクションログが減らない原因を知りたい
• log_reuse_wait_desc の値の意味を調べたい
• RDS for SQL Server でログバックアップはどうなっているのか

リスク表示の意味

• 参照のみデータと設定を変更しません。
• 低影響は限定的ですが、権限と負荷の確認が必要です。
• 中性能・ロック・コストに影響する可能性があります。
• 高障害・データ損失・復旧作業が発生する可能性があります。
• 専門家レビュー必須本番適用前に別途レビューが必須です。

GIIPの対応範囲

ログ使用率の確認そのものは、上記のSQLを1回実行するだけで終わります。難しいのは、複数インスタンスに対してこれを継続的に行い、`log_reuse_wait_desc` が NOTHING 以外に変わった瞬間を捉えることです。GIIP では AWS・Azure 上の複数データベースを対象に、AIエージェントが定期的に状態を取得し、閾値を超えた変化だけを人間の担当者に上げる運用を行っています。ログ肥大化のように「気付いた時には書き込みが止まっている」種類の事象は、値そのものより変化の検知が対応時間を左右します。

関連サービス

• AWSマネージド運用の対応範囲
• データベース性能チューニング