How to Check Transaction Log Usage on RDS for SQL Server

Applicability of This Document

Ready-to-Run Commands

-- 対象: 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';

How to Read the Results

When to Use This

• 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

Possible Causes (Most Likely First)

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.

Diagnostic Steps

1. 1

   Get log usage for all databases

   Read-only

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

2. 2

   Check `log_reuse_wait_desc`

   Read-only

   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

   Read-only

   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

   Read-only

   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

   Read-only

   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

   Read-only

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

Solutions

Differences by Version and Environment

If This Doesn't Resolve It

• 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.

Basis and Limitations of This Document

FAQ

Questions This Document Covers

• 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

What the Risk Labels Mean

• Read-onlyDoes not change data or settings.
• LowImpact is limited, but permissions and load should be checked.
• MediumMay affect performance, locking, or cost.
• HighMay cause an outage, data loss, or require recovery work.
• Expert Review RequiredRequires a separate review before applying to production.

GIIP's Scope of Support

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.

Related Services

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