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

   전체 데이터베이스의 로그 사용률을 가져온다

   参照のみ

   `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으로 돌아갑니다. 한 번의 값만으로 판단하지 마십시오.

対応方法

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

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

• 가장 오래된 트랜잭션의 시작 시각

  `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을 한 번 실행하면 끝납니다. 어려운 점은 여러 인스턴스에 대해 이를 지속적으로 수행하며, `log_reuse_wait_desc`가 NOTHING 이외로 바뀌는 순간을 포착하는 것입니다. GIIP에서는 AWS·Azure상의 여러 데이터베이스를 대상으로 AI 에이전트가 정기적으로 상태를 가져오고, 임계값을 초과한 변화만 담당자에게 전달하는 운영을 하고 있습니다. 로그 비대화처럼 "알아챘을 때는 쓰기가 멈춰 있는" 종류의 사건은 값 자체보다 변화 감지가 대응 시간을 좌우합니다.

関連サービス

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