Why MSrepl_commands Keeps Growing in SQL Server, and How to Check for Replication Lag

Applicability of This Document

Ready-to-Run 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';

How to Read the Results

When to Use This

• The distribution database keeps growing in size and is squeezing storage
• Data on the subscriber side has not been updated since a certain point in time
• The transaction log on the publisher side is not being released, and `log_reuse_wait_desc` stays at REPLICATION
• Distribution latency in Replication Monitor keeps increasing
• Snapshot application never finishes, or fails partway through

Possible Causes (Most Likely First)

1. 01

   The distribution agent cannot apply commands

   This is the most common cause. If application fails due to a constraint violation, a missing target row, insufficient permissions, or a dropped connection on the subscriber side, distribution stalls there and subsequent commands pile up. The error is recorded in the `comments` column of `MSdistribution_history`.

2. 02

   The cleanup job is not running

   If the `Distribution clean up: distribution` job is disabled, or keeps failing, already-distributed commands are not deleted and remain. The table keeps growing even if the agent itself is healthy.

3. 03

   The retention period is set too long

   A large `max_distretention` keeps distributed commands around for a long time. Sometimes this is intentionally set long as a safety margin, so judge whether the setting is appropriate together with operational requirements.

4. 04

   The subscriber has been down for a long time

   If the subscriber-side server is down or the network is disconnected, the corresponding commands back up in the distribution database. Since they are all delivered at once upon recovery, you need to account for the capacity needed during that window.

5. 05

   A large update happened all at once

   A bulk delete or bulk update generates one command per row. Updating millions of rows in a single operation grows `MSrepl_commands` proportionally. Splitting into batches smooths this out.

6. 06

   The same data is being distributed through multiple publications

   If the same table is included in multiple publications, commands are generated per publication. Check for configuration duplication (this depends on the configuration, so it is a hypothesis that needs verifying on the target environment).

Diagnostic Steps

1. 1

   Check the distribution agent's last activity time

   Read-only

   Join `MSdistribution_agents` with `MSdistribution_history` to identify agents with an old `last_history_time`.

2. 2

   Check recent run results and errors

   Read-only

   Use `runstatus` and `comments` to see whether it is stalled on an application error.

3. 3

   Get the pending command count

   Read-only

   Use `sp_replmonitorsubscriptionpendingcmds` to isolate whether the cause is on the distribution side or the cleanup side.

4. 4

   Check the cleanup job's history

   Read-only

   Check whether the job is enabled and has succeeded recently, in `msdb`.

5. 5

   Cross-check the retention period against the oldest data timestamp

   Read-only

   Compare `max_distretention` from `sp_helpdistributiondb` against `oldest_entry_time` in `MSrepl_transactions`.

6. 6

   Get the count trend over time

   Low

   A single `COUNT(*)` cannot show a growth trend. Sample it multiple times with a gap to confirm whether it keeps growing.

Solutions

Differences by Version and Environment

If This Doesn't Resolve It

• The Log Reader Agent on the publisher side

  If commands are not arriving in the distribution database at all, the cause lies with the Log Reader, not the distribution side. Check its running status.

• Blocking on the subscriber side

  If application is slow, check whether lock waits are occurring on the subscriber.

• Index fragmentation in the distribution database

  After a prolonged backlog, the index state on the distribution database side also becomes worth checking.

• Stability of the network path

  If retries keep repeating, the cause may be the network rather than the database.

Basis and Limitations of This Document

FAQ

Questions This Document Covers

• Checking Snapshot Replication lag
• I want to know why the distribution database keeps growing in size
• I want to isolate why data is not reaching a subscriber

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

A replication backlog stays hidden until the distribution database runs out of capacity, or until someone on the subscriber side notices the data looks stale. At GIIP, we periodically pull the distribution agent's last activity time and the pending command count, and notify the person in charge once the increase persists for a set duration. Operations tied directly to data consistency and business outages, like reinitialization, are excluded from automatic execution — detection, cause isolation, and scoping the impact are automated, but the decision to act remains human.

Related Services

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