How to Check Whether CDC Log Scanning Has Stopped in SQL Server

Applicability of This Document

Ready-to-Run Commands

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: sys.databases / sys.tables のメタデータ可視性
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
-- CDC が有効なデータベース
SELECT name AS database_name, is_cdc_enabled
FROM sys.databases
WHERE database_id > 4
ORDER BY name;
GO

-- キャプチャ対象テーブル
USE [SampleDB];
GO
SELECT
    s.name  AS schema_name,
    t.name  AS table_name,
    t.is_tracked_by_cdc
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
        ON t.schema_id = s.schema_id
WHERE t.is_tracked_by_cdc = 1
ORDER BY s.name, t.name;

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: 対象DBへの接続権限と VIEW DATABASE STATE
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
USE [SampleDB];
GO
SELECT
    session_id,            -- 0 はキャプチャジョブ開始以降の集計行
    start_time,
    end_time,
    duration,
    scan_phase,
    error_count,
    start_lsn,
    end_lsn,
    tran_count,
    last_commit_lsn,
    last_commit_time,
    latency,
    empty_scan_count,
    failed_sessions_count
FROM sys.dm_cdc_log_scan_sessions
ORDER BY session_id;

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: 対象DBへの接続権限（cdc スキーマへの参照権限）
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
USE [SampleDB];
GO
-- キャプチャ済みの最新トランザクション時刻
SELECT TOP (10)
    start_lsn,
    tran_begin_time,
    tran_end_time,
    tran_id
FROM cdc.lsn_time_mapping
ORDER BY tran_end_time DESC;

-- 現在の最大 LSN と、それに対応する時刻
SELECT
    sys.fn_cdc_get_max_lsn()                            AS max_lsn,
    sys.fn_cdc_map_lsn_to_time(sys.fn_cdc_get_max_lsn()) AS max_lsn_time;

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: 対象DBの db_owner または CDC メタデータへの参照権限
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
USE [SampleDB];
GO
-- キャプチャインスタンスの一覧（対象テーブル・変更テーブル名・キャプチャ列）
EXEC sys.sp_cdc_help_change_data_capture;
GO

-- 変更テーブルに実際に行が入っているか（キャプチャインスタンス名は環境に合わせる）
SELECT TOP (5)
    __$start_lsn,
    __$seqval,
    __$operation,     -- 1:削除 2:挿入 3:更新前 4:更新後
    __$update_mask
FROM cdc.dbo_SampleTable_CT
ORDER BY __$start_lsn DESC;

-- 対象: SQL Server 2008 以降（SQL Agent が利用できる環境）
-- 権限: 対象DBの db_owner、および msdb への参照権限
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
USE [SampleDB];
GO
-- CDC ジョブの構成（ポーリング間隔・保持期間など）
EXEC sys.sp_cdc_help_jobs;
GO

-- CDC ジョブの登録内容
SELECT
    job_type,
    database_id,
    maxtrans,
    maxscans,
    continuous,
    pollinginterval,
    retention,
    threshold
FROM msdb.dbo.cdc_jobs;
GO

-- SQL Agent ジョブとしての稼働状況と直近の実行結果
SELECT
    j.name              AS job_name,
    j.enabled,
    h.run_date,
    h.run_time,
    h.run_status,       -- 0:失敗 1:成功 2:再試行 3:取消 4:実行中
    h.message
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.sysjobhistory AS h
       ON j.job_id = h.job_id
      AND h.step_id = 0        -- ジョブ全体の結果
WHERE j.name LIKE 'cdc.%'
ORDER BY h.run_date DESC, h.run_time DESC;

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: 対象DBの db_owner（環境により sysadmin）
-- 変更作業: あり（キャプチャジョブの開始。滞留ログの読み取りが始まる）
-- Production 実行: 可能。滞留量が多い場合は I/O 増を見込むこと
USE [SampleDB];
GO
EXEC sys.sp_cdc_start_job @job_type = N'capture';

-- 停止する場合
-- EXEC sys.sp_cdc_stop_job @job_type = N'capture';

-- 対象: SQL Server 2008 以降 / Amazon RDS for SQL Server
-- 権限: 対象DBの db_owner（環境により sysadmin）
-- 変更作業: あり（キャプチャインスタンスと変更テーブルを削除）
-- Production 実行: 不可。下流システムへの影響評価と再同期計画を先に確定すること
USE [SampleDB];
GO
-- テーブル単位で CDC を無効化する
EXEC sys.sp_cdc_disable_table
     @source_schema   = N'dbo',
     @source_name     = N'SampleTable',
     @capture_instance = N'dbo_SampleTable';

-- データベース全体で CDC を無効化する（すべての変更テーブルが削除される）
-- EXEC sys.sp_cdc_disable_db;

How to Read the Results

When to Use This

• The transaction log keeps growing, and `log_reuse_wait_desc` stays fixed at REPLICATION
• The source table is being updated, but no new rows appear in `cdc.<capture_instance>_CT`
• Data reaching a downstream system has stopped as of a certain point in time
• CDC's capture job keeps failing repeatedly in SQL Agent

Possible Causes (Most Likely First)

1. 01

   The capture job has stopped

   This happens when SQL Agent is stopped, the job is disabled, or `sys.sp_cdc_stop_job` has been run manually. If the job is not running, the log scan does not progress and unread log is not released.

2. 02

   The capture job keeps failing with an error

   Schema changes to a captured table, insufficient permissions, or insufficient capacity on the change-table side can cause the job to fail, increasing `error_count` and `failed_sessions_count`. The cause is recorded in the `message` column of the job history.

3. 03

   CDC metadata is inconsistent after a database restore or attach

   Restoring or attaching a CDC-enabled database to a different instance can leave CDC state or jobs not carried over as expected. Check the restore options used, and whether the jobs exist after the restore.

4. 04

   Capture capacity cannot keep up with the volume of changes

   A burst of changes from a bulk update, depending on the `maxtrans` / `maxscans` / `pollinginterval` settings, can outpace capture's processing capacity, causing delay to accumulate. In this case the job is running, but `latency` keeps increasing.

5. 05

   Transactional replication and CDC coexist on the same database

   When transactional replication and CDC are used together on the same database, they share the behavior of the Log Reader. You need to isolate which one is behind `log_reuse_wait_desc = REPLICATION` by checking the state of both.

6. 06

   The cleanup job is not running, causing the change table to grow

   Even if capture is progressing, if the cleanup job has stopped, the `_CT` table keeps growing past its retention period. This is a different symptom from log bloat, but worth checking at the same time.

Diagnostic Steps

1. 1

   Check whether CDC is enabled and what is being captured

   Read-only

   Check `sys.databases.is_cdc_enabled` and `sys.tables.is_tracked_by_cdc`.

2. 2

   Look at the state of the log scan session

   Read-only

   Check `last_commit_time`, `latency`, and `error_count` in `sys.dm_cdc_log_scan_sessions`. This is the most direct indicator.

3. 3

   Measure the lag in captured time

   Read-only

   Check the gap between the maximum `tran_end_time` in `cdc.lsn_time_mapping` and the current time.

4. 4

   Check the job's running status and failure details

   Read-only

   Check `sys.sp_cdc_help_jobs` and `msdb.dbo.sysjobs` / `sysjobhistory`, looking at `enabled`, `run_status`, and `message`.

5. 5

   Cross-check against log-side symptoms

   Read-only

   If `sys.databases.log_reuse_wait_desc` is REPLICATION, CDC being stopped and log bloat are likely the same root cause.

6. 6

   Check for replication

   Read-only

   Check `is_published` / `is_subscribed` to isolate whether CDC or replication is holding the unread log.

Solutions

Differences by Version and Environment

If This Doesn't Resolve It

• The transaction log's `log_reuse_wait_desc`

  If the log is still not released after resuming CDC, check whether another factor (an open transaction, replication) coexists.

• Size and retention period of the change table (`_CT`)

  Check whether the cleanup job is running and whether the `retention` setting matches the read interval of downstream consumers.

• Read position of downstream systems

  Check which LSN the consuming side has read up to, and whether it falls within the range CDC retains.

• Operational status of the SQL Agent service

  Rule out the possibility that the job is not running because the Agent service itself is stopped, rather than a CDC-side problem.

Basis and Limitations of This Document

FAQ

Questions This Document Covers

• I want to check whether CDC capture is running
• log_reuse_wait_desc is not changing from REPLICATION
• I want to know why no data is appearing in CDC's _CT table

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

CDC stopping does not raise any error at the moment it happens. It typically only surfaces hours later, once the log file has ballooned and writes start failing. At GIIP, we pull the `last_commit_time` lag and the log's `log_reuse_wait_desc` together, and when both move in the same direction, we notify the person in charge with the cause already tied together. Operations that touch data consistency, like resuming capture, are excluded from automatic execution — only detection and cause isolation are automated.

Related Services

• 障害対応・火消し支援
• 24時間監視の対象を確認する