SQL Server Replication Monitoring Scripts

This is the set I reach for when replication starts acting up. Latency creeping, agents stuck, or subscribers falling behind — these queries cut through the noise fast. I’ve used them in production environments for years.

What’s Inside

1. Log Reader Agent latency and status
2. Distribution Agent latency and delivery metrics
3. Undistributed commands backlog
4. Publications and subscriptions overview
5. Practical troubleshooting steps

If you run transactional replication, keep these handy. They save time when things go sideways.

For the full picture on latency troubleshooting, see Microsoft’s guide: Effectively troubleshoot latency in SQL Server Transactional replication

1. Log Reader Agent Latency

The Log Reader pulls transactions from the Publisher log into the distribution database. Problems here hit everything downstream.

USE distribution;
GO

SELECT 
    a.name AS agent_name,
    CASE [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'
    END AS Status,
    [start_time],
    h.[time] AS log_time,
    [duration] AS duration_seconds,
    [comments],
    h.[xact_seqno] AS last_xact_seqno,
    [delivery_time],
    [delivered_transactions],
    [delivered_commands],
    [average_commands],
    [delivery_rate],
    [delivery_latency] AS delivery_latency_ms,
    [error_id],
    e.error_text
FROM [distribution].[dbo].[MSlogreader_history] h
JOIN MSlogreader_agents a ON a.id = h.agent_id
LEFT JOIN MSrepl_errors e ON e.id = h.error_id
ORDER BY h.time DESC;

Run this on the distribution database. High delivery_latency_ms or repeated errors usually point to log contention, large transactions, or connectivity between Publisher and Distributor.

2. Distribution Agent Latency

This agent pushes commands from distributor to subscribers. This is where subscriber latency often shows.

USE distribution;
GO

SELECT 
    a.name AS agent_name,
    CASE [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'
    END AS Status,
    [start_time],
    h.[time] AS log_time,
    [duration] AS duration_seconds,
    [comments],
    h.[xact_seqno],
    [current_delivery_rate],
    [current_delivery_latency],
    [delivered_transactions],
    [delivered_commands],
    [average_commands],
    [delivery_rate],
    [delivery_latency],
    [total_delivered_commands],
    [error_id],
    e.error_text
FROM MSdistribution_history h
JOIN MSdistribution_agents a ON a.id = h.agent_id
LEFT JOIN MSrepl_errors e ON e.id = h.error_id
ORDER BY h.time DESC;

Watch current_delivery_latency and overall delivery_rate. Slow rates often tie back to subscriber load, network, or resource pressure.

3. Undistributed Commands

Quick check for backlog:

USE Distribution;
GO

SELECT 
    publication AS PublicationName,
    subscriber_db AS SubscriberDatabase,
    COUNT(*) AS UndistributedCommands
FROM dbo.MSdistribution_status
GROUP BY publication, subscriber_db
ORDER BY UndistributedCommands DESC;

A growing number here means the Distribution Agent isn’t keeping up. Check agent status, subscriber availability, and system resources next.

4. Publications and Subscriptions

Basic overview:

Publications (on the Publisher database):

USE [YourPublicationDatabase];
GO

SELECT 
    name AS PublicationName,
    publication_type AS PublicationType,
    allow_push AS PushEnabled,
    allow_pull AS PullEnabled,
    allow_anonymous AS AllowAnonymous
FROM sys.publications;

Subscriptions (on the Distribution database):

USE Distribution;
GO

SELECT 
    publication AS PublicationName,
    subscriber_db AS SubscriberDatabase,
    subscriber AS SubscriberServer,
    subscription_type AS SubscriptionType,
    status AS SubscriptionStatus
FROM dbo.MSsubscriptions;

For a fuller topology view (articles, sizes, config options), query the distribution database directly or combine with publisher-side views.

5. Troubleshooting Replication Issues

Start here when things break:

• Open Replication Monitor first — it shows agent status, latency, and undistributed commands at a glance.
• Check Log Reader and Distribution Agent jobs. Stop and restart if they’re failed or idle.
• High undistributed commands? Look at the Distribution Agent history above, then CPU, memory, disk, and network on the Distributor and Subscriber.
• For stubborn errors, consider agent parameters like -SkipErrors temporarily, but fix the root cause.
• Review blocking, transaction log size, and distribution database maintenance.

Tracer tokens in Replication Monitor help measure true end-to-end latency. If it persists, gather agent logs and consider Microsoft Support.

These scripts run clean on current versions I’ve tested. Schedule the key ones via SQL Agent for regular checks and set alerts on thresholds that matter in your environment.