How to Check Transaction Log Usage on 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';

結果の読み方

こういう状況で使います

• Only the log file keeps growing, and storage free space keeps shrinking
• Writes fail with error 9002, "The transaction log for the database is full"
• Shrinking the log only causes it to grow back to its original size almost immediately
• Log usage does not drop even though automated backups are configured on Amazon RDS

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

1. 01

   Recovery model is FULL but no log backups are being taken (LOG_BACKUP)

   Under the full recovery model, log space is not reused until a log backup is taken. The most common cause of this symptom is setting the recovery model to FULL without ever setting up a log backup routine.

2. 02

   A transaction has been open for a long time (ACTIVE_TRANSACTION)

   Log records after the oldest uncommitted transaction cannot be truncated. This happens when an application leaves a `BEGIN TRAN` open, or a batch job has crashed and is mid-rollback.

3. 03

   Replication or CDC is holding unread log (REPLICATION)

   If the Log Reader Agent for transactional replication, or the CDC capture job, has stopped, log that has not yet been read stays unreleased.

4. 04

   Availability group / mirroring synchronization lag (AVAILABILITY_REPLICA / DATABASE_MIRRORING)

   Log is retained until it has been sent to and applied on the secondary. A stopped secondary or network lag causes log to accumulate.

5. 05

   A long-running backup or restore is in progress (ACTIVE_BACKUP_OR_RESTORE)

   The log cannot be truncated while a large database backup is running. Check whether the issue resolves once the backup completes.

6. 06

   A large update within a single transaction

   Running a bulk delete or update of tens of millions of rows as one transaction requires log space for the whole operation until it commits. Log size depends on how transactions are scoped.

確認手順

1. 1

   Get log usage for all databases

   参照のみ

   Run `DBCC SQLPERF(LOGSPACE)` to identify databases with high usage.

2. 2

   Check `log_reuse_wait_desc`

   参照のみ

   Query `sys.databases` and see whether the value persists as something other than NOTHING. This largely determines the category of the cause.

3. 3

   Cross-check the recovery model against log backup history

   参照のみ

   If `recovery_model_desc` is FULL, filter `msdb.dbo.backupset` on `type = 'L'` (log backup) to check the time of the most recent log backup.

4. 4

   Find any open transactions

   参照のみ

   If `log_reuse_wait_desc = ACTIVE_TRANSACTION`, identify the oldest transaction using `sys.dm_tran_active_transactions` or similar.

5. 5

   Check the state of CDC / replication

   参照のみ

   If `log_reuse_wait_desc = REPLICATION`, check whether the CDC capture job and the Log Reader Agent are running.

6. 6

   Re-sample every few minutes and watch the trend

   参照のみ

   A transient CHECKPOINT or LOG_SCAN value will return to NOTHING on the next sample. Do not judge based on a single reading.

対応方法

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

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

• Start time of the oldest transaction

  If ACTIVE_TRANSACTION persists, identify which session has had a transaction open, and since when.

• Status of the CDC capture job and Log Reader Agent

  If REPLICATION persists, check whether the capture side has stopped, or whether data is backing up on the distribution database side.

• Number of virtual log files (VLFs)

  Check the VLF count with `DBCC LOGINFO` (or `sys.dm_db_log_info` depending on version). An extremely high count slows down recovery and log processing.

• Free space and IOPS on the storage side

  When log growth is failing, the cause may lie with storage rather than the database itself.

この文書の根拠と限界

よくある質問

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

• I want to know why my SQL Server transaction log is not shrinking
• I want to look up the meaning of log_reuse_wait_desc values
• How do log backups work on RDS for SQL Server

リスク表示の意味

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

GIIPの対応範囲

Checking log usage itself is done the moment you run the SQL above once. The hard part is doing this continuously across multiple instances and catching the exact moment `log_reuse_wait_desc` changes to something other than NOTHING. At GIIP, AI agents periodically pull state from multiple databases on AWS and Azure, escalating only threshold-crossing changes to a human. For events like log bloat — where "by the time you notice, writes have already stopped" — detecting the change matters more than the value itself for response time.

関連サービス

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