SQL ServerでMSrepl_commandsが増え続ける原因とレプリケーション遅延の確認方法

-- 対象: SQL Server 2008 以降（ディストリビューター）
-- 権限: 配布データベースへの参照権限
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
USE [distribution];
GO
SELECT
    da.id                      AS agent_id,
    da.name                    AS agent_name,
    da.publisher_db,
    da.publication,
    da.subscriber_db,
    MAX(dh.time)               AS last_history_time,
    MAX(dh.delivered_commands) AS last_delivered_commands,
    MAX(dh.delivery_latency)   AS last_delivery_latency_ms
FROM dbo.MSdistribution_agents AS da
LEFT JOIN dbo.MSdistribution_history AS dh
       ON da.id = dh.agent_id
GROUP BY da.id, da.name, da.publisher_db, da.publication, da.subscriber_db
ORDER BY last_history_time ASC;

-- 対象: SQL Server 2008 以降（ディストリビューター）
-- 権限: 配布データベースへの参照権限
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
USE [distribution];
GO
SELECT TOP (50)
    dh.agent_id,
    da.name                    AS agent_name,
    dh.runstatus,              -- 1:開始 2:成功 3:実行中 4:アイドル 5:再試行 6:失敗
    dh.start_time,
    dh.time,
    dh.duration,
    dh.delivered_transactions,
    dh.delivered_commands,
    dh.delivery_rate,
    dh.delivery_latency,
    dh.comments
FROM dbo.MSdistribution_history AS dh
INNER JOIN dbo.MSdistribution_agents AS da
        ON dh.agent_id = da.id
ORDER BY dh.time DESC;

-- 対象: SQL Server 2008 以降（ディストリビューター）
-- 権限: replmonitor データベースロール または sysadmin
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
EXEC distribution.dbo.sp_replmonitorsubscriptionpendingcmds
     @publisher         = N'LEGACY-SQL01',
     @publisher_db      = N'SampleDB',
     @publication       = N'SamplePublication',
     @subscriber        = N'LEGACY-SQL01',
     @subscriber_db     = N'SampleDB',
     @subscription_type = 0;   -- 0: プッシュ / 1: プル

-- 対象: SQL Server 2008 以降（ディストリビューター）
-- 権限: 配布データベースへの参照権限
-- 変更作業: なし（参照のみ。ただし全件スキャンを伴う）
-- Production 実行: 可能。ただし件数が多いと実行時間が長くなるため負荷の低い時間帯を選ぶこと
USE [distribution];
GO
-- トランザクション側（entry_time で保持範囲が分かる）
SELECT
    t.publisher_database_id,
    COUNT(*)          AS transaction_count,
    MIN(t.entry_time) AS oldest_entry_time,
    MAX(t.entry_time) AS newest_entry_time
FROM dbo.MSrepl_transactions AS t
GROUP BY t.publisher_database_id;

-- コマンド側
SELECT
    c.publisher_database_id,
    COUNT(*) AS command_count
FROM dbo.MSrepl_commands AS c
GROUP BY c.publisher_database_id;

-- 対象: SQL Server 2008 以降（ディストリビューター）
-- 権限: msdb への参照権限
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT TOP (30)
    j.name        AS job_name,
    j.enabled,
    h.run_date,
    h.run_time,
    h.run_duration,
    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 'Distribution clean up%'
ORDER BY h.run_date DESC, h.run_time DESC;

-- 対象: SQL Server 2008 以降（ディストリビューター）
-- 権限: sysadmin または配布データベースの db_owner
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
EXEC sp_helpdistributiondb @database = N'distribution';

-- 対象: SQL Server 2008 以降（ディストリビューター）
-- 権限: sysadmin または配布データベースの db_owner
-- 変更作業: なし（参照のみ。ただし高コスト）
-- Production 実行: 推奨しない。xact_seqno の範囲を必ず絞ること
EXEC distribution.dbo.sp_browsereplcmds
     @xact_seqno_start      = '0x00000000000000000000',
     @xact_seqno_end        = '0x00000000000000000000',
     @publisher_database_id = 1;

-- 対象: SQL Server 2008 以降（ディストリビューター）
-- 権限: sysadmin
-- 変更作業: あり（配布データベースの保持ポリシー変更）
-- Production 実行: 可能。ただし短縮は未配布コマンド削除のリスクを伴う
-- 下の値は例示。現在の配布遅延を踏まえて決めること
EXEC sp_changedistributiondb
     @database = N'distribution',
     @property = N'max_distretention',
     @value    = 72;

EXEC sp_changedistributiondb
     @database = N'distribution',
     @property = N'history_retention',
     @value    = 48;

-- 対象: SQL Server 2008 以降（パブリッシャー側のパブリケーションDBで実行）
-- 権限: sysadmin または パブリケーションDBの db_owner
-- 変更作業: あり（滞留コマンドを破棄し、スナップショットからの再同期を要求）
-- Production 実行: 不可。影響評価と実施計画を確定させ、承認を得てから実施すること
USE [SampleDB];
GO
EXEC sp_reinitsubscription
     @publication    = N'SamplePublication',
     @subscriber     = N'LEGACY-SQL01',
     @destination_db = N'SampleDB';