DBA Scripts: Get Replication Agent Status

Publications Look Fine. Are the Agents Actually Keeping Up?

Replication Status tells you what’s published and subscribed. It doesn’t tell you whether the jobs actually moving that data, the Log Reader Agent and the Distribution Agent, are keeping up, or whether a backlog is quietly building between them.

This post covers three scripts that answer that: Log Reader Agent Status (is the reader keeping pace with the publisher’s transaction log), Distribution Agent Status (is delivery to subscribers keeping pace), and Undistributed Commands (how big is the backlog sitting between them right now). All three are safe to run from master on any instance: each one finds the distribution database itself, and returns a plain status row when replication is not configured.

Why Replication Agent Status Matters

• The Log Reader Agent and Distribution Agent are two separate jobs with two separate failure modes, a slow reader and a slow distributor look similar from a distance but need different fixes
• undistributed_commands is the single fastest number for “is there a backlog right now,” a high or growing count means delivery is falling behind regardless of which agent is at fault
• Both agent scripts read the last 24 hours of history. An agent that failed hours ago can already show Idle again, and the history rows are where the failure shows
• A publisher’s transaction log can’t truncate past what the Log Reader Agent hasn’t read yet, so a stalled reader is also a log that won’t truncate (log_reuse_wait_desc = REPLICATION), not only a lag problem

When to Run These Scripts

• Any time subscribers report stale or missing data despite publications and subscriptions showing Active
• Investigating a publisher’s transaction log that won’t reuse space, check whether the Log Reader Agent is the cause
• Routine replication health checks, alongside Replication Status
• After a distributor maintenance window or restart, to confirm agents resumed cleanly rather than assuming it

The Scripts

All three resolve the distribution database themselves, so you can run them from master on any instance without checking first.

Get-LogReaderAgentStatus: Is the Reader Keeping Up With the Publisher’s Log?

/*
Script Name : Get-LogReaderAgentStatus
Category    : high-availability
Purpose     : Monitors Log Reader Agent activity — status, delivery latency, transaction and command
              counts, and any replication errors. Returns the last 24 hours of history.
              Finds the distribution database automatically and returns a status row when
              replication is not configured, so it is safe to run from master on any instance.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-replication-agent-status/)
Requires    : db_owner or replmonitor role on the distribution database
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE is_distributor = 1)
BEGIN
    SELECT 'Replication is not configured on this instance (no distribution database).' AS status;
END
ELSE
BEGIN
    -- The distribution database is usually named "distribution" but can be renamed;
    -- resolve it by flag. Multiple distribution databases are possible but rare --
    -- this reads the first by name.
    DECLARE @distdb sysname =
        (SELECT TOP (1) name FROM sys.databases WHERE is_distributor = 1 ORDER BY name);

    DECLARE @sql nvarchar(max) = N'
    SELECT
        a.name AS agent_name,
        CASE h.runstatus
            WHEN 1 THEN ''Start''
            WHEN 2 THEN ''Succeed''
            WHEN 3 THEN ''In progress''
            WHEN 4 THEN ''Idle''
            WHEN 5 THEN ''Retry''
            WHEN 6 THEN ''Fail''
            ELSE ''Unknown''
        END AS status,
        h.start_time,
        h.[time] AS logged_at,
        h.duration AS duration_seconds,
        h.comments,
        h.xact_seqno AS last_sequence_number,
        h.delivery_time,
        h.delivered_transactions,
        h.delivered_commands,
        h.average_commands,
        h.delivery_rate AS avg_commands_per_sec,
        h.delivery_latency AS delivery_latency_ms,
        h.error_id,
        e.error_text
    FROM ' + QUOTENAME(@distdb) + N'.dbo.MSlogreader_history h
    JOIN ' + QUOTENAME(@distdb) + N'.dbo.MSlogreader_agents a ON a.id = h.agent_id
    LEFT JOIN ' + QUOTENAME(@distdb) + N'.dbo.MSrepl_errors e ON e.id = h.error_id
    WHERE h.[time] >= DATEADD(DAY, -1, GETDATE())
    ORDER BY h.[time] DESC;';

    EXEC sys.sp_executesql @sql;
END

Get-DistributionAgentStatus: Is Delivery to Subscribers Keeping Up?

/*
Script Name : Get-DistributionAgentStatus
Category    : high-availability
Purpose     : Monitors Distribution Agent activity — status, delivery latency (current and overall),
              transaction and command counts, and any replication errors. Returns the last 24 hours
              of history. Finds the distribution database automatically and returns a status row when
              replication is not configured, so it is safe to run from master on any instance.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-replication-agent-status/)
Requires    : db_owner or replmonitor role on the distribution database
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE is_distributor = 1)
BEGIN
    SELECT 'Replication is not configured on this instance (no distribution database).' AS status;
END
ELSE
BEGIN
    -- The distribution database is usually named "distribution" but can be renamed;
    -- resolve it by flag. Multiple distribution databases are possible but rare --
    -- this reads the first by name.
    DECLARE @distdb sysname =
        (SELECT TOP (1) name FROM sys.databases WHERE is_distributor = 1 ORDER BY name);

    DECLARE @sql nvarchar(max) = N'
    SELECT
        a.name AS agent_name,
        CASE h.runstatus
            WHEN 1 THEN ''Start''
            WHEN 2 THEN ''Succeed''
            WHEN 3 THEN ''In progress''
            WHEN 4 THEN ''Idle''
            WHEN 5 THEN ''Retry''
            WHEN 6 THEN ''Fail''
            ELSE ''Unknown''
        END AS status,
        h.start_time,
        h.[time] AS logged_at,
        h.duration AS duration_seconds,
        h.comments,
        h.xact_seqno AS last_sequence_number,
        h.current_delivery_rate AS current_commands_per_sec,
        h.current_delivery_latency AS current_latency_ms,
        h.delivered_transactions,
        h.delivered_commands,
        h.average_commands,
        h.delivery_rate AS avg_commands_per_sec,
        h.delivery_latency AS delivery_latency_ms,
        h.total_delivered_commands,
        h.error_id,
        e.error_text
    FROM ' + QUOTENAME(@distdb) + N'.dbo.MSdistribution_history h
    JOIN ' + QUOTENAME(@distdb) + N'.dbo.MSdistribution_agents a ON a.id = h.agent_id
    LEFT JOIN ' + QUOTENAME(@distdb) + N'.dbo.MSrepl_errors e ON e.id = h.error_id
    WHERE h.[time] >= DATEADD(DAY, -1, GETDATE())
    ORDER BY h.[time] DESC;';

    EXEC sys.sp_executesql @sql;
END

Get-UndistributedCommands: How Big Is the Backlog Right Now?

/*
Script Name : Get-UndistributedCommands
Category    : high-availability
Purpose     : Shows how many commands have been written to the distribution database but not yet
              delivered to each subscriber. A high and growing backlog means the Distribution
              Agent is lagging or has failed. Finds the distribution database automatically and
              returns a status row when replication is not configured, so it is safe to run from
              master on any instance.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-replication-agent-status/)
Requires    : db_owner or replmonitor role on the distribution database
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE is_distributor = 1)
BEGIN
    SELECT 'Replication is not configured on this instance (no distribution database).' AS status;
END
ELSE
BEGIN
    -- The distribution database is usually named "distribution" but can be renamed;
    -- resolve it by flag. Multiple distribution databases are possible but rare --
    -- this reads the first by name.
    DECLARE @distdb sysname =
        (SELECT TOP (1) name FROM sys.databases WHERE is_distributor = 1 ORDER BY name);

    -- MSdistribution_status is a VIEW that is already aggregated: one row per article per
    -- agent, carrying UndelivCmdsInDistDB (pending) and DelivCmdsInDistDB (delivered).
    -- SUM those columns -- COUNT(*) would return the number of articles, not the number of
    -- commands, which is a far smaller number that looks like a healthy backlog.
    -- The view carries no publication or subscriber name of its own; those live on
    -- MSdistribution_agents and are joined in on agent_id.
    DECLARE @sql nvarchar(max) = N'
    SELECT
        a.publisher_db AS publisher_database,
        a.publication AS publication_name,
        a.subscriber_db AS subscriber_database,
        SUM(s.UndelivCmdsInDistDB) AS undistributed_commands,
        SUM(s.DelivCmdsInDistDB) AS delivered_commands
    FROM ' + QUOTENAME(@distdb) + N'.dbo.MSdistribution_status s
    JOIN ' + QUOTENAME(@distdb) + N'.dbo.MSdistribution_agents a ON a.id = s.agent_id
    GROUP BY a.publisher_db, a.publication, a.subscriber_db
    ORDER BY undistributed_commands DESC;';

    EXEC sys.sp_executesql @sql;
END

How To Run From The Repo

Clone DBA Tools, initialize and run any of the three against the distribution database:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Log Reader Agent activity, last 24 hours:
.\run.ps1 Get-LogReaderAgentStatus

# Distribution Agent activity, last 24 hours:
.\run.ps1 Get-DistributionAgentStatus

# Current undistributed command backlog:
.\run.ps1 Get-UndistributedCommands

# To run against a remote sql server:
.\run.ps1 Get-DistributionAgentStatus -ServerInstance SQLSERVER01

These scripts live in the repo at:

• sql/high-availability/replication/Get-LogReaderAgentStatus.sql
• sql/high-availability/replication/Get-DistributionAgentStatus.sql
• sql/high-availability/replication/Get-UndistributedCommands.sql

Example Output

My lab has never been configured as a Distributor, so there is no distribution database on it at all. All three scripts say so plainly instead of failing: Replication is not configured on this instance (no distribution database). That is the same answer given by the Replication Status post. On an instance actually configured for replication, Get-DistributionAgentStatus returns a row like this (key columns shown):

Understanding the Results

Get-LogReaderAgentStatus and Get-DistributionAgentStatus

One row per history message in the last 24 hours, newest first. The two scripts share most columns; the ones only one returns are marked.

Get-UndistributedCommands

A point in time count, sorted biggest backlog first.

Best Practices

• Check Log Reader and Distribution Agent status together, a backlog can originate at either end and they need different fixes (reader can’t keep up with the publisher’s log vs. distributor can’t keep up with subscriber delivery)
• Treat a climbing undistributed_commands count as an active, worsening problem, not a number to check only when someone complains about stale data
• A stalled Log Reader Agent is also a transaction log growth risk on the publisher, cross-check with Log Reuse Waits if the publisher’s log won’t reuse space
• Bring error_text from the joined errors table when escalating an agent failure, it’s usually specific enough to point straight at the fix

Microsoft Learn: MSlogreader_history · MSdistribution_history · MSrepl_errors

Frequently Asked Questions

Summary

Publications and subscriptions showing healthy doesn’t guarantee the agents moving the actual data are keeping up. These three scripts check that directly: whether the Log Reader Agent is keeping pace with the publisher, whether the Distribution Agent is keeping pace with subscribers, and exactly how large the backlog between them is right now.

Run all three alongside Replication Status as a routine check, and treat a climbing undistributed command count as an active problem worth chasing immediately, not a number to glance at occasionally.

Related