DBA Scripts: Get Replication Agent Status

Peter Whyte

–

Part of the DBA-Tools Project.

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). Run all three against the distribution database (-Database distribution).

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 history tables retain the last 24 hours by default in these scripts, an agent that failed hours ago can already show as Idle again, the history is what shows the actual failure
  • A publisher’s transaction log can’t truncate past what the Log Reader Agent hasn’t yet read, a stalled reader is also a growing-transaction-log problem, not just a replication 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 run against the distribution database.

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.
              Run against the distribution database (-Database distribution).
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-replication-agent-status/)
Requires    : db_owner or replmonitor role on the distribution database
*/
-- Blog: https://sqldba.blog/dba-scripts-get-replication-agent-status/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

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 dbo.MSlogreader_history  h
JOIN dbo.MSlogreader_agents   a ON a.id = h.agent_id
LEFT JOIN dbo.MSrepl_errors   e ON e.id = h.error_id
WHERE h.[time] >= DATEADD(DAY, -1, GETDATE())
ORDER BY h.[time] DESC;

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. Run against the distribution database (-Database distribution).
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-replication-agent-status/)
Requires    : db_owner or replmonitor role on the distribution database
*/
-- Blog: https://sqldba.blog/dba-scripts-get-replication-agent-status/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

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 dbo.MSdistribution_history  h
JOIN dbo.MSdistribution_agents   a ON a.id = h.agent_id
LEFT JOIN dbo.MSrepl_errors      e ON e.id = h.error_id
WHERE h.[time] >= DATEADD(DAY, -1, GETDATE())
ORDER BY h.[time] DESC;

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

/*
Script Name : Get-UndistributedCommands
Category    : high-availability
Purpose     : Shows the count of commands that have been read from the publisher transaction log
              but not yet delivered to subscribers. A high and growing count indicates Distribution
              Agent lag or failure. Run against the distribution database (-Database distribution).
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-replication-agent-status/)
Requires    : db_owner or replmonitor role on the distribution database
*/
-- Blog: https://sqldba.blog/dba-scripts-get-replication-agent-status/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT
    publication                     AS publication_name,
    subscriber_db                   AS subscriber_database,
    COUNT(*)                        AS undistributed_commands
FROM dbo.MSdistribution_status
GROUP BY publication, subscriber_db
ORDER BY undistributed_commands DESC;

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 -Database distribution

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

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

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

These scripts live in the repo at:

Example Output

All three scripts fail the same honest way on this lab instance, not staged: connecting to the distribution database fails outright, Cannot open database "distribution" requested by the login. The login failed. That’s the correct result. HPAI01 has never been configured as a Distributor, so the distribution database doesn’t exist at all, the same honest gap already documented on the Replication Status post. On an instance actually configured for replication, Get-DistributionAgentStatus returns a row like this:

agent_name status current_commands_per_sec current_latency_ms delivered_commands
REPORT01-Sales-Sales_Transactional-1 Succeed 42 180 15302

Understanding the Results

  • status = Fail — check error_text via the joined MSrepl_errors table for the actual reason, don’t stop at “it failed”
  • status = Idle — the agent isn’t currently running, normal between scheduled runs, but check logged_at to confirm it ran recently rather than having stalled
  • current_delivery_latency / delivery_latency (Distribution Agent) — end-to-end milliseconds from commit at the publisher to applied at the subscriber; a rising trend here is the earliest sign of falling behind, before it shows up as a large undistributed_commands count
  • undistributed_commands high and climbing — a real, growing backlog; check whether the Distribution Agent itself is running (Get-DistributionAgentStatus) before assuming it’s just catching up from a burst

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

Related Scripts

You may also find these scripts useful:

Frequently Asked Questions

What’s the difference between Log Reader Agent and Distribution Agent status?

The Log Reader Agent reads committed transactions from the publisher’s transaction log into the distribution database. The Distribution Agent then delivers those commands from the distribution database to subscribers. A backlog can build at either stage, these two scripts isolate which one.

Why does undistributed_commands matter if the agents both show Succeed?

Succeed on the last run doesn’t mean there’s no backlog, it means the last run completed without erroring. A Distribution Agent that runs successfully but slower than new commands arrive still leaves a growing, real backlog, which is exactly what undistributed_commands surfaces directly.

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.

Blog Categories

Recent Posts

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *