SQL ServerのCDCでログスキャンが止まっていないか確認する方法

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