Monitoring Undelivered Distribution Commands in SQL Server Replication

Replication is a powerful SQL Server feature for distributing data across multiple servers. But one of the most common issues DBAs face is undelivered distribution commands — commands sitting in the distribution database waiting to be delivered to subscribers. Left unchecked, they can grow into replication latency, subscriber lag, and even system instability.

In this post, we’ll break down what undelivered commands are, why they pile up, and how you can automate alerts with custom scripts.

What Are Undelivered Commands?

In transactional replication, the Log Reader Agent moves transactions from the publisher’s log into the distribution database, and the Distribution Agent delivers them to subscribers.

If the distribution agent can’t keep up, you’ll see undelivered commands in the distribution database. This means:

Data changes are waiting to reach the subscriber(s).
Subscribers may be out-of-date (stale data).
Large backlogs can stress the distribution database and slow delivery further.

Why Do Commands Become Stuck?

Some common reasons:

Subscriber slowness – the subscriber server can’t apply changes quickly enough.
Blocking – long-running transactions or locks on the subscriber.
Network latency – bandwidth issues between distributor and subscriber.
Agent failures – distribution agent stopped, erroring out, or misconfigured.
Large transactions – bulk operations (e.g., mass deletes) causing heavy backlogs.

Step 1: Create a Tracking Table

USE DBAUTIL;
GO

CREATE TABLE [dbo].[REPLSTATUSCOMMANDS](
    [ServerName] NVARCHAR(50) NULL,
    [Article_ID] NCHAR(10) NULL,
    [Article] NVARCHAR(128) NULL,
    [Publication] NVARCHAR(128) NULL,
    [Name] NVARCHAR(50) NULL,
    [agent_id] NCHAR(10) NULL,
    [UndelivCmdsInDistDB] INT NULL,
    [DelivCmdsInDistDB] INT NULL,
    [TotalTrans] INT NULL
);

This table captures both delivered and undelivered command counts, broken down by article, publication, and agent

Step 2: Stored Procedure to Collect Undelivered Commands

USE DBAUTIL;
GO

CREATE OR ALTER PROCEDURE [dbo].[usp_GetReplStatusCommands]
AS
BEGIN
    SET NOCOUNT ON;

    TRUNCATE TABLE [DBAUTIL].[dbo].[REPLSTATUSCOMMANDS];

    INSERT INTO [DBAUTIL].[dbo].[REPLSTATUSCOMMANDS] (...)
    SELECT DISTINCT
           @@SERVERNAME,
           a.article_id,
           a.article,
           p.publication,
           SUBSTRING(agents.[name], 16, 35) AS [Name],
           s.agent_id,
           s.UndelivCmdsInDistDB,
           s.DelivCmdsInDistDB,
           UndelivCmdsInDistDB + DelivCmdsInDistDB AS TotalTrans
    FROM [distribution].dbo.MSdistribution_status AS s
        INNER JOIN [distribution].dbo.MSdistribution_agents AS agents
            ON agents.[id] = s.agent_id
        INNER JOIN [distribution].dbo.MSpublications AS p
            ON p.publication = agents.publication
        INNER JOIN [distribution].dbo.MSarticles AS a
            ON a.article_id = s.article_id
               AND p.publication_id = a.publication_id
    WHERE s.UndelivCmdsInDistDB <> 0
      AND agents.subscriber_db NOT LIKE '%virtual%';
END

This pulls undelivered command counts from the distribution database into your tracking table

Step 3: Automating Alerts with Database Mail

If undelivered commands exist, the procedure can build an HTML table and send it via Database Mail:

EXEC msdb.dbo.sp_send_dbmail 
     @profile_name = 'SQL Mail',
     @recipients = 'DBAGroup@yourcompany.com',
     @subject = 'Replication Latency: Undelivered Commands in Distributor',
     @body = @tableHTML,
     @body_format = 'HTML';

Example Email (Basic Version)

ServerName	Article_ID	Article	Publication	Name	agent_id	UndelivCmdsInDistDB	DelivCmdsInDistDB	TotalTrans
PROD-SQL01	15	Orders	Pub_Sales	DistAgent001	101	1500	500	2000

Hands-On Workshop

Follow this step-by-step to deploy and test:

Deploy objects – Create the DBAUTIL database, table, and stored procedure.
Run the proc manually –

EXEC DBAUTIL.dbo.usp_GetReplStatusCommands;
SELECT * FROM DBAUTIL.dbo.REPLSTATUSCOMMANDS;

Configure Database Mail – Test with a dummy email.
Simulate latency – Pause the Distribution Agent, rerun the proc, confirm rows and email.
Schedule a job – Use SQL Agent to run the proc every 10 minutes.

SQL Agent Job Script

USE msdb;
GO

EXEC sp_add_job
    @job_name = N'Replication Undelivered Commands Alert',
    @enabled = 1;

EXEC sp_add_jobstep
    @job_name = N'Replication Undelivered Commands Alert',
    @step_name = N'Run usp_GetReplStatusCommands',
    @subsystem = N'TSQL',
    @command = N'EXEC DBAUTIL.dbo.usp_GetReplStatusCommands;',
    @database_name = N'DBAUTIL';

EXEC sp_add_schedule
    @schedule_name = N'Every 10 Minutes',
    @freq_type = 4,
    @freq_interval = 1,
    @freq_subday_type = 4,
    @freq_subday_interval = 10;

EXEC sp_attach_schedule
    @job_name = N'Replication Undelivered Commands Alert',
    @schedule_name = N'Every 10 Minutes';

EXEC sp_add_jobserver
    @job_name = N'Replication Undelivered Commands Alert',
    @server_name = N'(LOCAL)';

Enhancements

Threshold-Based Alerts

Modify the WHERE clause to only fire when UndelivCmdsInDistDB > 1000.

Blocking Checks

Extend the procedure with blocking queries to see if locks are holding up delivery.

NEW: Summary Counts with Severity

Add a quick header summary to the email showing counts of Warnings and Critical issues:

DECLARE @WarningCount INT, @CriticalCount INT;

SELECT @WarningCount = COUNT(*) 
FROM DBAUTIL.dbo.REPLSTATUSCOMMANDS 
WHERE UndelivCmdsInDistDB BETWEEN 501 AND 5000;

SELECT @CriticalCount = COUNT(*) 
FROM DBAUTIL.dbo.REPLSTATUSCOMMANDS 
WHERE UndelivCmdsInDistDB > 5000;

SET @Summary = 
    N'<h3>Replication Undelivered Command Status</h3>' +
    CASE WHEN @WarningCount = 0 AND @CriticalCount = 0
         THEN '✅ All clear: No warnings or critical issues.'
         ELSE CONCAT(
             '⚠️ ', @WarningCount, ' Warning(s)<br>',
             '🔴 ', @CriticalCount, ' Critical issue(s)'
         )
    END;

Then prepend @Summary before your HTML table output.

Color-Coded Rows

Add CSS classes for green/yellow/red rows in your table output:

Green: 0–500 undelivered
Yellow: 501–5000 undelivered
Red: >5000 undelivered

This makes critical rows pop out visually in the alert email.

Example Email (Enhanced Version)

Summary:
⚠️ 1 Warning(s)
🔴 2 Critical issue(s)

ServerName	Article_ID	Article	Publication	Name	agent_id	UndelivCmdsInDistDB	DelivCmdsInDistDB	TotalTrans
PROD-SQL01	15	Orders	Pub_Sales	DistAgent001	101	250 (green row)	120	370
PROD-SQL01	18	Invoice	Pub_Sales	DistAgent002	102	1800 (yellow row)	500	2300
PROD-SQL01	20	Audit	Pub_Sales	DistAgent003	103	6000 (red row)	200	6200

Summary

Undelivered commands indicate replication lag.
Causes range from subscriber slowness to blocking and network issues.
By capturing undelivered counts from MSdistribution_status and automating alerts, DBAs can proactively respond before subscribers fall too far behind.
The enhanced version adds severity summary and color-coded rows so DBAs can triage issues faster.

References

Microsoft Docs – Monitor Replication Agents
Microsoft Docs – MSdistribution_status (Transact-SQL)
Microsoft Docs – Replication Latency and Validation

optional: Full email and HTML

IF EXISTS (SELECT 1 FROM DBAUTIL.dbo.REPLSTATUSCOMMANDS)
BEGIN
    DECLARE 
        @WarningCount  INT,
        @CriticalCount INT,
        @Summary       NVARCHAR(MAX),
        @BodyHtml      NVARCHAR(MAX);

    /* --- Counts for header --- */
    SELECT @WarningCount = COUNT(*)
    FROM DBAUTIL.dbo.REPLSTATUSCOMMANDS
    WHERE UndelivCmdsInDistDB BETWEEN 501 AND 5000;

    SELECT @CriticalCount = COUNT(*)
    FROM DBAUTIL.dbo.REPLSTATUSCOMMANDS
    WHERE UndelivCmdsInDistDB > 5000;

    /* --- Header block (simple HTML, Outlook-safe) --- */
    SET @Summary =
        N'<div style="font-family:Arial,Helvetica,sans-serif;margin:0 0 8px 0;">
            <div style="font-size:16px;color:#003399;font-weight:bold;margin:0 0 4px 0;">
                Replication Undelivered Command Status
            </div>' +
            CASE WHEN @WarningCount = 0 AND @CriticalCount = 0
                 THEN N'<div style="font-size:13px;">✅ All clear: No warnings or critical issues detected.</div>'
                 ELSE N'<div style="font-size:13px;">' +
                        CASE WHEN @WarningCount  > 0 THEN N'⚠️ ' + CAST(@WarningCount  AS varchar(10)) + N' Warning(s)<br/>' ELSE N'' END +
                        CASE WHEN @CriticalCount > 0 THEN N'🔴 ' + CAST(@CriticalCount AS varchar(10)) + N' Critical issue(s)' ELSE N'' END +
                      N'</div>'
            END +
        N'</div>';

    /* --- Table with inline styles + Severity column --- */
    SET @BodyHtml = 
      @Summary +
      N'<table style="border-collapse:collapse;width:100%;font-family:Arial,Helvetica,sans-serif;font-size:12px;">
          <tr>
            <th style="background:#003399;color:#fff;padding:6px;text-align:left;border:1px solid #ddd;">Severity</th>
            <th style="background:#003399;color:#fff;padding:6px;text-align:left;border:1px solid #ddd;">ServerName</th>
            <th style="background:#003399;color:#fff;padding:6px;text-align:left;border:1px solid #ddd;">Article_ID</th>
            <th style="background:#003399;color:#fff;padding:6px;text-align:left;border:1px solid #ddd;">Article</th>
            <th style="background:#003399;color:#fff;padding:6px;text-align:left;border:1px solid #ddd;">Publication</th>
            <th style="background:#003399;color:#fff;padding:6px;text-align:left;border:1px solid #ddd;">Name</th>
            <th style="background:#003399;color:#fff;padding:6px;text-align:left;border:1px solid #ddd;">agent_id</th>
            <th style="background:#003399;color:#fff;padding:6px;text-align:left;border:1px solid #ddd;">UndelivCmdsInDistDB</th>
            <th style="background:#003399;color:#fff;padding:6px;text-align:left;border:1px solid #ddd;">DelivCmdsInDistDB</th>
            <th style="background:#003399;color:#fff;padding:6px;text-align:left;border:1px solid #ddd;">TotalTrans</th>
          </tr>' +
      CAST((
        SELECT
          /* inline row color so Outlook honors it */
          CASE 
            WHEN UndelivCmdsInDistDB > 5000 THEN 'style="background-color:#ffe0e0;"'
            WHEN UndelivCmdsInDistDB > 500  THEN 'style="background-color:#fff5cc;"'
            ELSE 'style="background-color:#eaf7ea;"'
          END AS [@style],

          /* Severity cell with emoji text */
          td = CASE 
                  WHEN UndelivCmdsInDistDB > 5000 THEN N'🔴 Critical'
                  WHEN UndelivCmdsInDistDB > 500  THEN N'⚠️ Warning'
                  ELSE N'✅ OK'
               END, '',

          /* Data cells */
          td = ServerName, '',
          td = Article_ID, '',
          td = Article, '',
          td = Publication, '',
          td = [Name], '',
          td = agent_id, '',
          td = UndelivCmdsInDistDB, '',
          td = DelivCmdsInDistDB, '',
          td = TotalTrans, ''

        FROM DBAUTIL.dbo.REPLSTATUSCOMMANDS
        FOR XML PATH('tr'), TYPE
      ) AS NVARCHAR(MAX))
      + N'</table>';

    /* --- Send the message (make sure HTML is set) --- */
    EXEC msdb.dbo.sp_send_dbmail
         @profile_name = 'SQL Mail',                    -- update if needed
         @recipients   = 'DBAGroup@yourcompany.com',    -- update recipients
         @subject      = 'Replication Latency: Undelivered Commands in Distributor',
         @body         = @BodyHtml,
         @body_format  = 'HTML';
END

Or with CSS if outlook strips the colors

IF EXISTS (SELECT 1 FROM DBAUTIL.dbo.REPLSTATUSCOMMANDS)
BEGIN
    DECLARE 
        @WarningCount  INT,
        @CriticalCount INT,
        @Summary       NVARCHAR(MAX),
        @BodyHtml      NVARCHAR(MAX);

    /* --- Counts for header --- */
    SELECT @WarningCount = COUNT(*)
    FROM DBAUTIL.dbo.REPLSTATUSCOMMANDS
    WHERE UndelivCmdsInDistDB BETWEEN 501 AND 5000;

    SELECT @CriticalCount = COUNT(*)
    FROM DBAUTIL.dbo.REPLSTATUSCOMMANDS
    WHERE UndelivCmdsInDistDB > 5000;

    /* --- Header block (simple HTML, Outlook-safe) --- */
    SET @Summary =
        N'<div style="font-family:Arial,Helvetica,sans-serif;margin:0 0 8px 0;">
            <div style="font-size:16px;color:#003399;font-weight:bold;margin:0 0 4px 0;">
                Replication Undelivered Command Status
            </div>' +
            CASE WHEN @WarningCount = 0 AND @CriticalCount = 0
                 THEN N'<div style="font-size:13px;">✅ All clear: No warnings or critical issues detected.</div>'
                 ELSE N'<div style="font-size:13px;">' +
                        CASE WHEN @WarningCount  > 0 THEN N'⚠️ ' + CAST(@WarningCount  AS varchar(10)) + N' Warning(s)<br/>' ELSE N'' END +
                        CASE WHEN @CriticalCount > 0 THEN N'🔴 ' + CAST(@CriticalCount AS varchar(10)) + N' Critical issue(s)' ELSE N'' END +
                      N'</div>'
            END +
        N'</div>';

    /* --- Table with inline styles + Severity column --- */
    SET @BodyHtml = 
      @Summary +
      N'<table style="border-collapse:collapse;width:100%;font-family:Arial,Helvetica,sans-serif;font-size:12px;">
          <tr>
            <th style="background:#003399;color:#fff;padding:6px;text-align:left;border:1px solid #ddd;">Severity</th>
            <th style="background:#003399;color:#fff;padding:6px;text-align:left;border:1px solid #ddd;">ServerName</th>
            <th style="background:#003399;color:#fff;padding:6px;text-align:left;border:1px solid #ddd;">Article_ID</th>
            <th style="background:#003399;color:#fff;padding:6px;text-align:left;border:1px solid #ddd;">Article</th>
            <th style="background:#003399;color:#fff;padding:6px;text-align:left;border:1px solid #ddd;">Publication</th>
            <th style="background:#003399;color:#fff;padding:6px;text-align:left;border:1px solid #ddd;">Name</th>
            <th style="background:#003399;color:#fff;padding:6px;text-align:left;border:1px solid #ddd;">agent_id</th>
            <th style="background:#003399;color:#fff;padding:6px;text-align:left;border:1px solid #ddd;">UndelivCmdsInDistDB</th>
            <th style="background:#003399;color:#fff;padding:6px;text-align:left;border:1px solid #ddd;">DelivCmdsInDistDB</th>
            <th style="background:#003399;color:#fff;padding:6px;text-align:left;border:1px solid #ddd;">TotalTrans</th>
          </tr>' +
      CAST((
        SELECT
          /* inline row color so Outlook honors it */
          CASE 
            WHEN UndelivCmdsInDistDB > 5000 THEN 'style="background-color:#ffe0e0;"'
            WHEN UndelivCmdsInDistDB > 500  THEN 'style="background-color:#fff5cc;"'
            ELSE 'style="background-color:#eaf7ea;"'
          END AS [@style],

          /* Severity cell with emoji text */
          td = CASE 
                  WHEN UndelivCmdsInDistDB > 5000 THEN N'🔴 Critical'
                  WHEN UndelivCmdsInDistDB > 500  THEN N'⚠️ Warning'
                  ELSE N'✅ OK'
               END, '',

          /* Data cells */
          td = ServerName, '',
          td = Article_ID, '',
          td = Article, '',
          td = Publication, '',
          td = [Name], '',
          td = agent_id, '',
          td = UndelivCmdsInDistDB, '',
          td = DelivCmdsInDistDB, '',
          td = TotalTrans, ''

        FROM DBAUTIL.dbo.REPLSTATUSCOMMANDS
        FOR XML PATH('tr'), TYPE
      ) AS NVARCHAR(MAX))
      + N'</table>';

    /* --- Send the message (make sure HTML is set) --- */
    EXEC msdb.dbo.sp_send_dbmail
         @profile_name = 'SQL Mail',                    -- update if needed
         @recipients   = 'DBAGroup@yourcompany.com',    -- update recipients
         @subject      = 'Replication Latency: Undelivered Commands in Distributor',
         @body         = @BodyHtml,
         @body_format  = 'HTML';
END

Discover more from SQLyard

Subscribe to get the latest posts sent to your email.

Monitoring Undelivered Distribution Commands in SQL Server Replication

What Are Undelivered Commands?

Why Do Commands Become Stuck?

Step 1: Create a Tracking Table

Step 2: Stored Procedure to Collect Undelivered Commands

Step 3: Automating Alerts with Database Mail

Example Email (Basic Version)

Hands-On Workshop

SQL Agent Job Script

Enhancements

Threshold-Based Alerts

Blocking Checks

NEW: Summary Counts with Severity

Color-Coded Rows

Example Email (Enhanced Version)

Summary

References

Like this:

Related

Discover more from SQLyard

Leave a ReplyCancel reply

Sign up to receive email updates, fresh news and more!

What Are Undelivered Commands?

Why Do Commands Become Stuck?

Step 1: Create a Tracking Table

Step 2: Stored Procedure to Collect Undelivered Commands

Step 3: Automating Alerts with Database Mail

Example Email (Basic Version)

Hands-On Workshop

SQL Agent Job Script

Enhancements

Threshold-Based Alerts

Blocking Checks

NEW: Summary Counts with Severity

Color-Coded Rows

Example Email (Enhanced Version)

Summary

References

Share this:

Like this:

Related

Discover more from SQLyard

Related Posts

Leave a ReplyCancel reply

Discover more from SQLyard