Replication is great until it gets quiet and drifts out of sync. The fix is simple: collect errors and backlog on a schedule, keep a local audit trail, and email the team only when action is needed. This guide gives you a full solution you can paste into SSMS and run.

You will:

Log replication failures and retries from the Distributor
Track pending commands and ETA per subscription
Avoid duplicate emails with a watermark and a “lag signature”
Get one SQL Agent job that runs every 5 minutes

What you need before you start

Run on the Distributor server, or from a central admin instance that can reach the Distributor.
Database Mail configured and the profile name you plan to use.
Permissions
- Read access on distribution database system tables
- db_owner on your admin database for objects below
- SQL Agent rights to create jobs and schedules
Optional if the Distributor is remote: a linked server to it.

Example linked server (only if you need it)See Practical Alternatives at the bottom:

-- Only if you must execute remotely to the Distributor
EXEC master.dbo.sp_addlinkedserver
     @server = N'DIST01',
     @srvproduct = N'SQL Server';
-- If needed, add credentials with sp_addlinkedsrvlogin

Quick setup checklist

Create or pick an admin database, for example DBA_Admin
Create the log tables
Create the capture procedures
Create the state table to suppress duplicate emails
Create the email procedure that respects the state
Create the “capture + alert” SQL Agent job
Test with low thresholds to force an email
Use the “How to use” queries and FAQ to operate day to day

0) Variables you will reuse

Replace these once, then paste all scripts.

DECLARE @AdminDb       sysname = N'DBA_Admin';
DECLARE @DbMailProfile sysname = N'DBA Mail';
DECLARE @NotifyTo      nvarchar(400) = N'dba-team@yourco.com';

1) Tables to store issues and backlog

-- Create the admin DB if it does not exist
IF DB_ID(@AdminDb) IS NULL
BEGIN
    EXEC('CREATE DATABASE ' + QUOTENAME(@AdminDb));
END
GO

-- Replication issues
EXEC('
USE ' + QUOTENAME(@AdminDb) + ';
IF OBJECT_ID(''dbo.Repl_Issues'',''U'') IS NULL
BEGIN
  CREATE TABLE dbo.Repl_Issues
  (
      issue_id            bigint IDENTITY(1,1) PRIMARY KEY,
      collected_at        datetime2(3) NOT NULL DEFAULT sysdatetime(),
      agent_type          varchar(20)   NULL,      -- Distribution, LogReader, Snapshot, Merge
      runstatus           int           NULL,      -- 1=Start,2=Succeed,3=In progress,4=Idle,5=Retry,6=Fail
      publication         sysname       NULL,
      publisher_db        sysname       NULL,
      subscriber_name     sysname       NULL,
      subscriber_db       sysname       NULL,
      error_code          int           NULL,
      error_text          nvarchar(4000) NULL,
      comments            nvarchar(4000) NULL,
      xact_seqno          varbinary(16) NULL,
      agent_id            int           NULL,
      job_id              uniqueidentifier NULL
  );
  CREATE INDEX IX_Repl_Issues_collected_at ON dbo.Repl_Issues(collected_at DESC);
  CREATE INDEX IX_Repl_Issues_err ON dbo.Repl_Issues(error_code, runstatus);
END
');

-- Pending command snapshots
EXEC('
USE ' + QUOTENAME(@AdminDb) + ';
IF OBJECT_ID(''dbo.Repl_PendingCmds'',''U'') IS NULL
BEGIN
  CREATE TABLE dbo.Repl_PendingCmds
  (
      collected_at         datetime2(3) NOT NULL DEFAULT sysdatetime(),
      publisher            sysname      NOT NULL,
      publisher_db         sysname      NOT NULL,
      publication          sysname      NOT NULL,
      subscriber           sysname      NOT NULL,
      subscriber_db        sysname      NOT NULL,
      subscription_type    int          NOT NULL,   -- 0=Push,1=Pull
      pendingcmdcount      int          NULL,
      estimatedprocesstime int          NULL
  );
END
');
GO

2) Capture replication issues from the Distributor

This collects Distribution Agent failures and retries, plus Log Reader issues. It also joins agent metadata so you get publication and subscriber info.

DECLARE @sql nvarchar(max) = N'
USE ' + QUOTENAME(@AdminDb) + ';
IF OBJECT_ID(''dbo.Capture_Replication_Issues'',''P'') IS NOT NULL
    DROP PROCEDURE dbo.Capture_Replication_Issues;
GO
CREATE PROCEDURE dbo.Capture_Replication_Issues
    @DistributionDb sysname = N''distribution'',
    @SinceMinutes   int     = 1440,
    @IncludeLogReader bit   = 1
AS
BEGIN
    SET NOCOUNT ON;

    ;WITH hist AS
    (
        SELECT h.agent_id, h.runstatus, h.[time], h.comments, h.error_id, h.xact_seqno
        FROM ' + QUOTENAME('distribution') + N'.dbo.MSdistribution_history AS h
        WHERE (h.runstatus IN (5,6) OR h.error_id IS NOT NULL)
          AND h.[time] >= DATEADD(MINUTE, -@SinceMinutes, SYSUTCDATETIME())
    ),
    dist AS
    (
        SELECT a.id AS agent_id, a.publication, a.publisher_db, a.subscriber_db, a.subscriber_name,
               TRY_CAST(a.job_id AS uniqueidentifier) AS job_id
        FROM ' + QUOTENAME('distribution') + N'.dbo.MSdistribution_agents AS a
    ),
    errs AS
    (
        SELECT e.id AS error_id, e.[time] AS error_time, e.error_code, e.error_text, e.xact_seqno
        FROM ' + QUOTENAME('distribution') + N'.dbo.MSrepl_errors AS e
    )
    INSERT dbo.Repl_Issues
    (
        agent_type, runstatus, publication, publisher_db, subscriber_name, subscriber_db,
        error_code, error_text, comments, xact_seqno, agent_id, job_id
    )
    SELECT 
        agent_type = ''Distribution'',
        i.runstatus,
        d.publication, d.publisher_db, d.subscriber_name, d.subscriber_db,
        e.error_code, e.error_text,
        i.comments,
        i.xact_seqno,
        i.agent_id,
        d.job_id
    FROM hist AS i
    JOIN dist AS d ON d.agent_id = i.agent_id
    LEFT JOIN errs AS e ON e.error_id = i.error_id;

    IF @IncludeLogReader = 1
    BEGIN
        ;WITH lr AS
        (
            SELECT h.agent_id, h.runstatus, h.[time], h.comments
            FROM ' + QUOTENAME('distribution') + N'.dbo.MSlogreader_history AS h
            WHERE (h.runstatus IN (5,6))
              AND h.[time] >= DATEADD(MINUTE, -@SinceMinutes, SYSUTCDATETIME())
        ),
        lra AS
        (
            SELECT a.id AS agent_id, a.publisher_db
            FROM ' + QUOTENAME('distribution') + N'.dbo.MSlogreader_agents AS a
        )
        INSERT dbo.Repl_Issues
        (
            agent_type, runstatus, publication, publisher_db, subscriber_name, subscriber_db,
            error_code, error_text, comments, xact_seqno, agent_id, job_id
        )
        SELECT
            ''LogReader'',
            lr.runstatus,
            NULL,
            lra.publisher_db,
            NULL,
            NULL,
            NULL,
            NULL,
            lr.comments,
            NULL,
            lr.agent_id,
            NULL
        FROM lr
        JOIN lra ON lra.agent_id = lr.agent_id;
    END
END
';
EXEC(@sql);
GO

Runstatus quick map: 1 Start, 2 Succeed, 3 In progress, 4 Idle, 5 Retry, 6 Fail.

3) Capture pending commands for each subscription

Single-subscription capture:

EXEC('
USE ' + QUOTENAME(@AdminDb) + ';
CREATE OR ALTER PROCEDURE dbo.Capture_PendingCommands
    @publisher sysname,
    @publisher_db sysname,
    @publication sysname,
    @subscriber sysname,
    @subscriber_db sysname,
    @subscription_type int
AS
BEGIN
    SET NOCOUNT ON;
    CREATE TABLE #pc(pendingcmdcount int, estimatedprocesstime int);

    INSERT #pc
    EXEC distribution.dbo.sp_replmonitorsubscriptionpendingcmds
         @publisher=@publisher, @publisher_db=@publisher_db, @publication=@publication,
         @subscriber=@subscriber, @subscriber_db=@subscriber_db, @subscription_type=@subscription_type;

    INSERT dbo.Repl_PendingCmds
    (publisher, publisher_db, publication, subscriber, subscriber_db, subscription_type,
     pendingcmdcount, estimatedprocesstime)
    SELECT @publisher, @publisher_db, @publication, @subscriber, @subscriber_db, @subscription_type,
           pendingcmdcount, estimatedprocesstime
    FROM #pc;
END
');

All subscriptions capture:

EXEC('
USE ' + QUOTENAME(@AdminDb) + ';
CREATE OR ALTER PROCEDURE dbo.Capture_All_PendingCommands
AS
BEGIN
    SET NOCOUNT ON;

    ;WITH subs AS (
        SELECT
            publisher       = da.publisher,
            publisher_db    = da.publisher_db,
            publication     = da.publication,
            subscriber      = da.subscriber_name,
            subscriber_db   = da.subscriber_db,
            subscription_type = da.subscription_type
        FROM distribution.dbo.MSdistribution_agents AS da
    )
    SELECT * INTO #subs FROM subs;

    DECLARE @publisher sysname, @publisher_db sysname, @publication sysname,
            @subscriber sysname, @subscriber_db sysname, @subscription_type int;

    DECLARE c CURSOR LOCAL FAST_FORWARD FOR
        SELECT publisher, publisher_db, publication, subscriber, subscriber_db, subscription_type
        FROM #subs;

    OPEN c;
    FETCH NEXT FROM c INTO @publisher, @publisher_db, @publication, @subscriber, @subscriber_db, @subscription_type;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            EXEC dbo.Capture_PendingCommands
                 @publisher=@publisher, @publisher_db=@publisher_db, @publication=@publication,
                 @subscriber=@subscriber, @subscriber_db=@subscriber_db, @subscription_type=@subscription_type;
        END TRY
        BEGIN CATCH
            -- keep going even if one subscription has an issue
        END CATCH;

        FETCH NEXT FROM c INTO @publisher, @publisher_db, @publication, @subscriber, @subscriber_db, @subscription_type;
    END

    CLOSE c; DEALLOCATE c;
END
');

4) State table to avoid duplicate emails

We avoid repeats in two ways:

New error watermark: remember the highest issue_id emailed
Lag signature: hash the current lagging set so we only email when the lag picture changes

EXEC('
USE ' + QUOTENAME(@AdminDb) + ';
IF OBJECT_ID(''dbo.Repl_Alerts_State'',''U'') IS NULL
BEGIN
  CREATE TABLE dbo.Repl_Alerts_State
  (
      id                 int IDENTITY(1,1) PRIMARY KEY,
      last_issue_id      bigint NULL,
      last_sent_at       datetime2(0) NULL,
      last_lag_signature varbinary(32) NULL,
      last_lag_sent_at   datetime2(0) NULL
  );
  INSERT dbo.Repl_Alerts_State(last_issue_id, last_sent_at, last_lag_signature, last_lag_sent_at)
  VALUES (NULL, NULL, NULL, NULL);
END
');

5) Email procedure that respects the state

Builds a small HTML report
Only sends when there are new issues or a changed lag signature
Thresholds are parameters so you can tune easily

DECLARE @emailProc nvarchar(max) = N'
USE ' + QUOTENAME(@AdminDb) + ';
CREATE OR ALTER PROCEDURE dbo.Send_Replication_Health_Email
    @DbMailProfile sysname,
    @EmailTo       nvarchar(400),
    @PendingCmdThreshold int = 5000,
    @EtaSecondsThreshold  int = 600
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @now datetime2(0) = SYSDATETIME();
    DECLARE @last_issue_id bigint = (SELECT TOP(1) last_issue_id FROM dbo.Repl_Alerts_State ORDER BY id);

    /* New errors or retries since last watermark */
    ;WITH issues AS (
        SELECT i.issue_id, i.collected_at, i.agent_type, i.publication, i.publisher_db,
               i.subscriber_name, i.subscriber_db, i.runstatus, i.error_code,
               err_text = COALESCE(NULLIF(LTRIM(RTRIM(i.error_text)),''''), i.comments)
        FROM dbo.Repl_Issues AS i
        WHERE (i.runstatus IN (5,6) OR i.error_code IS NOT NULL)
          AND (@last_issue_id IS NULL OR i.issue_id > @last_issue_id)
    )
    SELECT TOP (100) *
    INTO #new_issues
    FROM issues
    ORDER BY issue_id DESC;

    /* Latest lag view and threshold filter */
    ;WITH latest AS (
        SELECT *, ROW_NUMBER() OVER (
            PARTITION BY publisher, publisher_db, publication, subscriber, subscriber_db, subscription_type
            ORDER BY collected_at DESC
        ) rn
        FROM dbo.Repl_PendingCmds
    )
    SELECT *
    INTO #lagging
    FROM latest
    WHERE rn = 1
      AND (pendingcmdcount >= @PendingCmdThreshold OR estimatedprocesstime >= @EtaSecondsThreshold);

    /* Build a signature for the lagging set so we do not resend the same alert repeatedly */
    DECLARE @lagConcat nvarchar(max) = N'''';
    SELECT @lagConcat = @lagConcat
         + ISNULL(publication,N'''') + N''|''
         + ISNULL(publisher,N'''') + N''|''
         + ISNULL(publisher_db,N'''') + N''|''
         + ISNULL(subscriber,N'''') + N''|''
         + ISNULL(subscriber_db,N'''') + N''|''
         + CAST(ISNULL(subscription_type, -1) AS nvarchar(10)) + N''|''
         + CAST(ISNULL(pendingcmdcount, -1) AS nvarchar(20)) + N''|''
         + CAST(ISNULL(estimatedprocesstime, -1) AS nvarchar(20)) + N'';'' 
    FROM #lagging
    ORDER BY pendingcmdcount DESC, estimatedprocesstime DESC;

    DECLARE @newLagSig varbinary(32) = CASE WHEN @lagConcat = N'''' THEN NULL ELSE HASHBYTES(''SHA2_256'', @lagConcat) END;
    DECLARE @oldLagSig varbinary(32) = (SELECT TOP(1) last_lag_signature FROM dbo.Repl_Alerts_State ORDER BY id);

    DECLARE @hasIssues bit = CASE WHEN EXISTS (SELECT 1 FROM #new_issues) THEN 1 ELSE 0 END;
    DECLARE @hasLag    bit = CASE WHEN EXISTS (SELECT 1 FROM #lagging)    THEN 1 ELSE 0 END;
    DECLARE @lagChanged bit = CASE WHEN @newLagSig IS NULL AND @oldLagSig IS NULL THEN 0
                                   WHEN @newLagSig IS NULL AND @oldLagSig IS NOT NULL THEN 1
                                   WHEN @newLagSig IS NOT NULL AND @oldLagSig IS NULL THEN 1
                                   WHEN @newLagSig <> @oldLagSig THEN 1 ELSE 0 END;

    IF (@hasIssues = 0 AND (@hasLag = 0 OR @lagChanged = 0))
        RETURN;

    /* Build HTML body */
    DECLARE @body nvarchar(max) = N''
<h2>SQL Replication Health</h2>
<p>Generated at '' + CONVERT(nvarchar(19), @now, 120) + N''</p>'';

    IF @hasIssues = 1
    BEGIN
        SET @body += N''<h3>New Failures or Retries</h3>
<table border="1" cellspacing="0" cellpadding="4">
<tr><th>ID</th><th>When</th><th>Agent</th><th>Publication</th><th>Pub DB</th><th>Subscriber</th><th>Sub DB</th><th>Status</th><th>Error</th></tr>'';

        SELECT @body +=
            N''<tr><td>'' + CAST(issue_id AS nvarchar(20)) + N''</td>''
          + N''<td>'' + CONVERT(nvarchar(19), collected_at, 120) + N''</td>''
          + N''<td>'' + ISNULL(agent_type,N'''') + N''</td>''
          + N''<td>'' + ISNULL(publication,N'''') + N''</td>''
          + N''<td>'' + ISNULL(publisher_db,N'''') + N''</td>''
          + N''<td>'' + ISNULL(subscriber_name,N'''') + N''</td>''
          + N''<td>'' + ISNULL(subscriber_db,N'''') + N''</td>''
          + N''<td>'' + CAST(runstatus AS nvarchar(10)) + N''</td>''
          + N''<td>'' + ISNULL(err_text,N'''') + N''</td></tr>''
        FROM #new_issues
        ORDER BY issue_id DESC;

        SET @body += N''</table>'';
    END

    IF @hasLag = 1
    BEGIN
        SET @body += N''<h3>Lagging Subscriptions</h3>
<p>Thresholds: pending ≥ '' + CAST(@PendingCmdThreshold AS nvarchar(20)) +
N'' or ETA ≥ '' + CAST(@EtaSecondsThreshold AS nvarchar(20)) + N'' sec</p>
<table border="1" cellspacing="0" cellpadding="4">
<tr><th>When</th><th>Publication</th><th>Publisher</th><th>Pub DB</th><th>Subscriber</th><th>Sub DB</th><th>Type</th><th>Pending</th><th>ETA (s)</th></tr>'';

        SELECT @body +=
             N''<tr><td>'' + CONVERT(nvarchar(19), collected_at, 120) + N''</td>''
           + N''<td>'' + publication + N''</td>''
           + N''<td>'' + publisher + N''</td>''
           + N''<td>'' + publisher_db + N''</td>''
           + N''<td>'' + subscriber + N''</td>''
           + N''<td>'' + subscriber_db + N''</td>''
           + N''<td>'' + CASE subscription_type WHEN 0 THEN N''Push'' WHEN 1 THEN N''Pull'' ELSE N''?'' END + N''</td>''
           + N''<td>'' + COALESCE(CAST(pendingcmdcount AS nvarchar(20)),N'''') + N''</td>''
           + N''<td>'' + COALESCE(CAST(estimatedprocesstime AS nvarchar(20)),N'''') + N''</td></tr>''
        FROM #lagging
        ORDER BY pendingcmdcount DESC, estimatedprocesstime DESC;

        SET @body += N''</table>'';
    END

    EXEC msdb.dbo.sp_send_dbmail
         @profile_name = @DbMailProfile,
         @recipients   = @EmailTo,
         @subject      = N''[ALERT] Replication issues or lag detected'',
         @body_format  = ''HTML'',
         @body         = @body;

    /* Update state to avoid duplicate emails */
    UPDATE s
      SET last_issue_id      = (SELECT MAX(issue_id) FROM dbo.Repl_Issues),
          last_sent_at       = CASE WHEN @hasIssues = 1 THEN @now ELSE s.last_sent_at END,
          last_lag_signature = CASE WHEN @hasLag = 1 THEN @newLagSig ELSE s.last_lag_signature END,
          last_lag_sent_at   = CASE WHEN @hasLag = 1 THEN @now ELSE s.last_lag_sent_at END
    FROM dbo.Repl_Alerts_State AS s;
END
';
EXEC(@emailProc);
GO

6) SQL Agent job that runs every 5 minutes

Three steps: capture issues, capture backlog, email if needed.

DECLARE @JobName sysname = N'Replication Health – Collector and Alert';

-- Clean up if re-running
IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = @JobName)
BEGIN
  EXEC msdb.dbo.sp_delete_job @job_name = @JobName, @delete_unused_schedule = 1;
END
GO

DECLARE @cmd1 nvarchar(max) = N'
EXEC ' + QUOTENAME(@AdminDb) + N'.dbo.Capture_Replication_Issues
     @SinceMinutes = 10,     -- window overlap
     @IncludeLogReader = 1;';

DECLARE @cmd2 nvarchar(max) = N'
EXEC ' + QUOTENAME(@AdminDb) + N'.dbo.Capture_All_PendingCommands;';

DECLARE @cmd3 nvarchar(max) = N'
EXEC ' + QUOTENAME(@AdminDb) + N'.dbo.Send_Replication_Health_Email
     @DbMailProfile = N''' + @DbMailProfile + N''',
     @EmailTo       = N''' + @NotifyTo + N''',
     @PendingCmdThreshold = 5000,   -- tune
     @EtaSecondsThreshold  = 600;   -- tune
';

DECLARE @job_id uniqueidentifier;
EXEC msdb.dbo.sp_add_job
     @job_name=@JobName,
     @enabled=1,
     @description=N'Collects replication issues and backlog, emails on change.',
     @start_step_id=1,
     @owner_login_name=N'sa',
     @job_id=@job_id OUTPUT;

EXEC msdb.dbo.sp_add_jobstep
     @job_id=@job_id, @step_id=1, @step_name=N'Capture Issues (10m)',
     @subsystem=N'TSQL', @database_name=N'master', @command=@cmd1,
     @on_success_action=3, @on_fail_action=2;

EXEC msdb.dbo.sp_add_jobstep
     @job_id=@job_id, @step_id=2, @step_name=N'Capture Pending Commands',
     @subsystem=N'TSQL', @database_name=N'master', @command=@cmd2,
     @on_success_action=3, @on_fail_action=2;

EXEC msdb.dbo.sp_add_jobstep
     @job_id=@job_id, @step_id=3, @step_name=N'Send Email If Problems',
     @subsystem=N'TSQL', @database_name=N'master', @command=@cmd3,
     @on_success_action=1, @on_fail_action=2;

EXEC msdb.dbo.sp_add_schedule
     @schedule_name=N'Every 5 minutes',
     @enabled=1,
     @freq_type=4,
     @freq_interval=1,
     @freq_subday_type=4,
     @freq_subday_interval=5,
     @active_start_time=0;

EXEC msdb.dbo.sp_attach_schedule @job_id=@job_id, @schedule_name=N'Every 5 minutes';
EXEC msdb.dbo.sp_add_jobserver @job_id=@job_id, @server_name = N'(LOCAL)';
GO

7) How to use it day to day

View the last errors that were collected

SELECT TOP (50)
  collected_at, agent_type, runstatus, publication, publisher_db,
  subscriber_name, subscriber_db, error_code, error_text, comments, xact_seqno
FROM  DBA_Admin.dbo.Repl_Issues
ORDER BY issue_id DESC;

View the current lag snapshot

;WITH latest AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY publisher, publisher_db, publication, subscriber, subscriber_db, subscription_type
    ORDER BY collected_at DESC
  ) rn
  FROM DBA_Admin.dbo.Repl_PendingCmds
)
SELECT collected_at, publication, publisher, publisher_db, subscriber, subscriber_db,
       subscription_type, pendingcmdcount, estimatedprocesstime
FROM latest WHERE rn = 1
ORDER BY pendingcmdcount DESC;

;WITH latest AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY publisher, publisher_db, publication, subscriber, subscriber_db, subscription_type
    ORDER BY collected_at DESC
  ) rn
  FROM DBA_Admin.dbo.Repl_PendingCmds
)
SELECT collected_at, publication, publisher, publisher_db, subscriber, subscriber_db,
       subscription_type, pendingcmdcount, estimatedprocesstime
FROM latest WHERE rn = 1
ORDER BY pendingcmdcount DESC;

Drill into the exact failing command by seqno

-- Use xact_seqno from an error row
USE distribution;
EXEC sp_browsereplcmds
     @xact_seqno_start = 0x00000274000010A0000A00000000,
     @xact_seqno_end   = 0x00000274000010A0000A00000000;

Check for inactive subscriptions

USE distribution;
SELECT s.publisher_id, s.publication_id, s.subscriber_id, s.subscriber_db, s.status
FROM dbo.MSsubscriptions AS s
WHERE s.status = 0;  -- 0 Inactive, 1 Subscribed, 2 Active

8) Testing

Test Database Mail with a simple message first.

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = @DbMailProfile,
     @recipients   = @NotifyTo,
     @subject      = N'Test mail',
     @body         = N'If you see this, DbMail works.';

Force an alert by temporarily lowering thresholds.

EXEC DBA_Admin.dbo.Send_Replication_Health_Email
     @DbMailProfile = @DbMailProfile,
     @EmailTo       = @NotifyTo,
     @PendingCmdThreshold = 1,
     @EtaSecondsThreshold = 1;

Create a benign test “issue” row if you want to simulate an error path.

INSERT DBA_Admin.dbo.Repl_Issues(agent_type, runstatus, publication, publisher_db, error_code, error_text)
VALUES ('Distribution', 6, 'TestPub', 'TestDB', 99999, 'Test simulated failure');

Run the email proc again and verify one email arrives. It should not resend the same error on the next run because the watermark advances.

9) How duplicate emails are avoided

New error watermark: the procedure records the highest issue_id seen when it sends an email about errors. Next run only considers rows with a higher issue_id. That prevents repeats for the same errors.
Lag signature: the procedure hashes the current lagging set entries. If the hash is unchanged, it skips sending another lag email. When the set changes or pending counts move, it will email again. This keeps you informed about changes without noise every 5 minutes.

You can clear the watermark and signature if you want to force a fresh alert:

UPDATE DBA_Admin.dbo.Repl_Alerts_State
SET last_issue_id = NULL, last_lag_signature = NULL;

10) Tuning tips

Start with @PendingCmdThreshold = 5000 and @EtaSecondsThreshold = 600
Tighten thresholds on publications that must be near real time
Widen them for bulk workloads that legitimately queue up
If a subscription is always near the threshold, consider a per-publication threshold design. The email procedure can be extended to read a tiny config table with publication-specific limits.

11) Troubleshooting

No email arrives

Check msdb.dbo.sysmail_allitems for send status
Confirm Database Mail profile name is correct
Look for blocked external SMTP or credential issues

“Could not find stored procedure sp_replmonitorsubscriptionpendingcmds”

Confirm you are on a Distributor
Confirm you are calling distribution.dbo.sp_replmonitorsubscriptionpendingcmds exactly

“The EXECUTE permission was denied”

Grant execute on the admin procs to the login that the job uses
Ensure read permissions on distribution tables

Job runs but tables are empty

Make sure replication history exists. The first hour after setup might be quiet if nothing is moving
Lower @SinceMinutes or raise it depending on your schedule

12) Reference queries you will use a lot

Top 10 publications by backlog right now

;WITH latest AS (
  SELECT publication, pendingcmdcount,
         ROW_NUMBER() OVER (PARTITION BY publication ORDER BY collected_at DESC) rn
  FROM DBA_Admin.dbo.Repl_PendingCmds
)
SELECT TOP (10) publication, pendingcmdcount
FROM latest WHERE rn = 1
ORDER BY pendingcmdcount DESC;

Last 24 hours of failures

SELECT collected_at, agent_type, publication, subscriber_name, error_code, error_text
FROM DBA_Admin.dbo.Repl_Issues
WHERE collected_at >= DATEADD(HOUR, -24, SYSDATETIME())
  AND (runstatus IN (5,6) OR error_code IS NOT NULL)
ORDER BY collected_at DESC;

Summary

You now have a simple but complete replication monitor:

A local history of failures and retries
A backlog tracker with pending commands and ETA
Smart alerts that avoid duplicates by remembering what was sent
A single job that runs every 5 minutes

This is enough to move you from reactive firefighting to calm, proactive checks.

Final thoughts

Keep the thresholds realistic so you do not train the team to ignore email. Watch the backlog trend for a week and adjust. If you outgrow this, add a small config table for per-publication thresholds, and extend the email to include suggested actions. If your environment is large, consider a separate job that captures every 2 minutes and an email job that runs every 10 minutes. The code above will support both.

Practical Alternatives (no linked server)

1) `OPENROWSET` / `OPENDATASOURCE` (Ad-hoc distributed queries)

When to use: You want pure T-SQL, minimal setup, and your security team allows the Ad-hoc Distributed Queries feature.

Pros: Simple, no server object created, runs inside your existing job steps.
Cons: Requires enabling Ad-hoc; you must allow the OLE DB provider.

Setup once (on your admin/collector instance):

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;

-- Allow the SQL OLE DB provider if needed (MSOLEDBSQL is preferred)
EXEC master.dbo.sp_MSset_oledb_prop N'MSOLEDBSQL', N'AllowInProcess', 1;
EXEC master.dbo.sp_MSset_oledb_prop N'MSOLEDBSQL', N'DynamicParameters', 1;

Use it (example read straight from remote Distributor):

-- Read Distribution Agent failures/retries (no linked server)
INSERT DBA_Admin.dbo.Repl_Issues (agent_type, runstatus, publication, publisher_db,
                                  subscriber_name, subscriber_db, error_code, error_text, comments,
                                  xact_seqno, agent_id, job_id)
SELECT 'Distribution', h.runstatus, a.publication, a.publisher_db,
       a.subscriber_name, a.subscriber_db, e.error_code, e.error_text, h.comments,
       h.xact_seqno, h.agent_id, TRY_CAST(a.job_id AS uniqueidentifier)
FROM OPENROWSET(
       'MSOLEDBSQL',
       'Server=DIST01;Trusted_Connection=Yes;Encrypt=Yes;TrustServerCertificate=Yes;',
       'SELECT h.runstatus, h.time, h.comments, h.error_id, h.xact_seqno, h.agent_id,
               a.publication, a.publisher_db, a.subscriber_name, a.subscriber_db, a.job_id,
               e.error_code, e.error_text
        FROM distribution.dbo.MSdistribution_history h
        JOIN distribution.dbo.MSdistribution_agents a ON a.id=h.agent_id
        LEFT JOIN distribution.dbo.MSrepl_errors e ON e.id=h.error_id
        WHERE h.runstatus IN (5,6) OR h.error_id IS NOT NULL'
     ) AS R;

Swap DIST01 for your Distributor. If you use SQL auth: ...;User ID=...;Password=...;.

You can do the same for sp_replmonitorsubscriptionpendingcmds by wrapping it in a temp table on the remote side via a subquery, or (simpler) use PowerShell (next option) for that proc’s two-column result.

2) SQL Agent PowerShell step (no SQL engine cross-server features)

When to use: Ad-hoc queries are disallowed, or you prefer scriptable client connections.
Pros: No SQL engine features to enable; TLS/ODBC/ODBC Driver 18 supported; easy to transform and bulk insert.
Cons: Adds a PowerShell dependency to the job step.

Job step script (PowerShell) example:

# ----- EDIT THESE -----
$Distributor = "DIST01"
$AdminServer = "ADMIN01"        # where DBA_Admin lives (can be same box)
$AdminDb     = "DBA_Admin"
# ----------------------

# 1) Pull failures/retries from Distributor
$queryIssues = @"
SELECT h.time, h.runstatus, a.publication, a.publisher_db, a.subscriber_name, a.subscriber_db,
       h.error_id, e.error_code, e.error_text, h.comments, h.xact_seqno, h.agent_id, a.job_id
FROM distribution.dbo.MSdistribution_history h
JOIN distribution.dbo.MSdistribution_agents a ON a.id = h.agent_id
LEFT JOIN distribution.dbo.MSrepl_errors e ON e.id = h.error_id
WHERE h.time >= DATEADD(MINUTE, -10, SYSUTCDATETIME())
  AND (h.runstatus IN (5,6) OR h.error_id IS NOT NULL);
"@

$dataIssues = Invoke-Sqlcmd -ServerInstance $Distributor -Database "distribution" -Query $queryIssues -Encrypt -TrustServerCertificate

# 2) Bulk insert into local staging, then merge to Repl_Issues
if ($dataIssues.Rows.Count -gt 0) {
  $connStr = "Server=$AdminServer;Database=$AdminDb;Trusted_Connection=True;Encrypt=True;TrustServerCertificate=True;"
  $bulk = New-Object Data.SqlClient.SqlBulkCopy($connStr)
  $bulk.DestinationTableName = "dbo.Repl_Issues_Staging"
  $bulk.WriteToServer($dataIssues)
  Invoke-Sqlcmd -ServerInstance $AdminServer -Database $AdminDb -Query "EXEC dbo.Upsert_Repl_Issues_From_Staging;"
}

# 3) Pending commands for each subscription
$querySubs = "SELECT publisher, publisher_db, publication, subscriber_name, subscriber_db, subscription_type FROM distribution.dbo.MSdistribution_agents;"
$subs = Invoke-Sqlcmd -ServerInstance $Distributor -Database "distribution" -Query $querySubs -Encrypt -TrustServerCertificate
foreach ($s in $subs) {
  $pc = Invoke-Sqlcmd -ServerInstance $Distributor -Database "distribution" -Query @"
EXEC sp_replmonitorsubscriptionpendingcmds
  @publisher = N'$($s.publisher)', @publisher_db = N'$($s.publisher_db)',
  @publication = N'$($s.publication)', @subscriber = N'$($s.subscriber_name)',
  @subscriber_db = N'$($s.subscriber_db)', @subscription_type = $($s.subscription_type);
"@ -Encrypt -TrustServerCertificate

  if ($pc.Rows.Count -gt 0) {
    # Add identity columns to match dbo.Repl_PendingCmds (one row expected)
    $row = $pc.Rows[0]
    $insert = @"
INSERT dbo.Repl_PendingCmds(publisher, publisher_db, publication, subscriber, subscriber_db, subscription_type, pendingcmdcount, estimatedprocesstime)
VALUES (N'$($s.publisher)', N'$($s.publisher_db)', N'$($s.publication)', N'$($s.subscriber_name)', N'$($s.subscriber_db)', $($s.subscription_type), $( [int]$row.pendingcmdcount ), $( [int]$row.estimatedprocesstime ));
"@
    Invoke-Sqlcmd -ServerInstance $AdminServer -Database $AdminDb -Query $insert
  }
}

In your DB, add a tiny dbo.Repl_Issues_Staging table and a merge proc dbo.Upsert_Repl_Issues_From_Staging (or just INSERT if duplicates aren’t a concern).

3) `SQLCMD` + file drop + `BULK INSERT`

When to use: You like “dumb pipes”—export on Distributor, import locally; works even across domains using a shared UNC.

Pros: Dead simple, auditable CSVs, zero engine features needed.
Cons: Requires a share; a bit more plumbing.

Agent CmdExec steps (two-step pattern):

REM Step A on ADMIN server: pull CSV from Distributor
sqlcmd -S DIST01 -d distribution -E -Q "SET NOCOUNT ON;
SELECT h.time, h.runstatus, a.publication, a.publisher_db, a.subscriber_name, a.subscriber_db,
       ISNULL(e.error_code,0) AS error_code, ISNULL(e.error_text,''), h.comments
FROM distribution.dbo.MSdistribution_history h
JOIN distribution.dbo.MSdistribution_agents a ON a.id=h.agent_id
LEFT JOIN distribution.dbo.MSrepl_errors e ON e.id=h.error_id
WHERE h.time >= DATEADD(MINUTE,-10,SYSUTCDATETIME())
  AND (h.runstatus IN (5,6) OR h.error_id IS NOT NULL);" -s "," -W -o "\\fileshare\repl\issues.csv"

-- Step B on ADMIN server: import CSV
BULK INSERT DBA_Admin.dbo.Repl_Issues_Staging
FROM '\\fileshare\repl\issues.csv'
WITH (FORMAT='CSV', FIRSTROW=2, FIELDTERMINATOR=',', ROWTERMINATOR='\n', CODEPAGE='65001');
EXEC DBA_Admin.dbo.Upsert_Repl_Issues_From_Staging;

Do the same pattern for pending commands (export each sub into CSV, then bulk insert).

4) PolyBase / External Table over ODBC (SQL 2019+)

When to use: You’re comfortable installing PolyBase and want “virtual tables” to the Distributor—no linked server object, fully securable.

Pros: Query like local tables; granular security; no LS.
Cons: Heavier setup (PolyBase engine + services); you must define schemas.

Sketch (high-level):

-- Create DB scoped credential if using SQL auth (or use integrated)
CREATE DATABASE SCOPED CREDENTIAL RemoteDistCred
WITH IDENTITY = 'dist_user', SECRET = 'dist_password';

-- External data source via ODBC (Driver 18 recommended)
CREATE EXTERNAL DATA SOURCE DistSql
WITH (
  LOCATION = 'odbc://DIST01',
  CONNECTION_OPTIONS = 'Driver={ODBC Driver 18 for SQL Server};Encrypt=Yes;TrustServerCertificate=Yes;',
  CREDENTIAL = RemoteDistCred
);

-- External table mapping
CREATE EXTERNAL TABLE dbo.MSdistribution_history_ext (
  -- define columns to match remote
  agent_id        int,
  [time]          datetime,
  runstatus       int,
  comments        nvarchar(4000),
  error_id        int,
  xact_seqno      varbinary(16)
)
WITH ( DATA_SOURCE = DistSql, LOCATION = 'distribution.dbo.MSdistribution_history' );

-- Query like local
SELECT TOP (100) * FROM dbo.MSdistribution_history_ext;

Which one should you choose?

Option	Easiest	No Engine Feature	Secure/TLS	Good for Procs (pendingcmds)	Notes
OPENROWSET/OPENDATASOURCE	✅	❌ (Ad-hoc needed)	✅	⚠️ (awkward for proc calls)	Great for pure SELECTs
Agent PowerShell	✅	✅	✅	✅	Most flexible, no engine toggles
SQLCMD + BULK INSERT	✅	✅	✅	✅	Nice for air-gapped/file-based flows
PolyBase External Table	⚠️	✅	✅	⚠️	Heavier install, “virtual tables”

My default recommendation: use Agent PowerShell on your admin server (or even on the Distributor) to pull the data with Invoke-Sqlcmd and write into your local tables. It’s clean, fast, and doesn’t require enabling Ad-hoc queries or creating server-level objects.

How to plug this into your existing blog/runbook

In your “What you need before you start” section, change the last bullet to: Optional if the Distributor is remote: pick one access method — (A) OPENROWSET Ad-hoc, (B) SQL Agent PowerShell, (C) SQLCMD+CSV, or (D) PolyBase External Tables. Linked server is not required.
In your job steps, replace the “remote query” calls with one of the patterns above:
- If you pick PowerShell: swap Step 1 and Step 2 to PowerShell job steps (or keep T-SQL collectors for local Distributor and use PS only when remote).
- If you pick OPENROWSET: replace FROM distribution... with the OPENROWSET('MSOLEDBSQL', 'Server=...','SELECT ...') form.
- If you pick SQLCMD + BULK: add a CmdExec step to export, then a T-SQL step to BULK INSERT, then your existing email step.

Security & reliability tips (quick)

Use Windows auth wherever possible; if SQL auth, encrypt and avoid storing creds in plain text (PowerShell SecureString/DPAPI or Credential Manager if feasible).
For OPENROWSET, prefer MSOLEDBSQL and set Encrypt=Yes;TrustServerCertificate=Yes; (or load a proper CA cert).
For PowerShell, install the SQLServer module (or use built-in Invoke-Sqlcmd on SQL Server boxes).
Log failures to your admin DB so your email job can include “meta-errors” (e.g., couldn’t reach Distributor).

Discover more from SQLYARD

Subscribe to get the latest posts sent to your email.

Proactive SQL Replication Monitoring: Errors, Backlog, and Smart Email Alerts

What you need before you start

Quick setup checklist

0) Variables you will reuse

2) Capture replication issues from the Distributor

3) Capture pending commands for each subscription

4) State table to avoid duplicate emails

5) Email procedure that respects the state

6) SQL Agent job that runs every 5 minutes

7) How to use it day to day

8) Testing

9) How duplicate emails are avoided

10) Tuning tips

11) Troubleshooting

12) Reference queries you will use a lot

Summary

Final thoughts

1) `OPENROWSET` / `OPENDATASOURCE` (Ad-hoc distributed queries)

2) SQL Agent PowerShell step (no SQL engine cross-server features)

3) `SQLCMD` + file drop + `BULK INSERT`

4) PolyBase / External Table over ODBC (SQL 2019+)

Which one should you choose?

How to plug this into your existing blog/runbook

Security & reliability tips (quick)

Like this:

Related

Discover more from SQLYARD

Leave a Comment Cancel Reply

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

What you need before you start

Quick setup checklist

0) Variables you will reuse

2) Capture replication issues from the Distributor

3) Capture pending commands for each subscription

4) State table to avoid duplicate emails

5) Email procedure that respects the state

6) SQL Agent job that runs every 5 minutes

7) How to use it day to day

8) Testing

9) How duplicate emails are avoided

10) Tuning tips

11) Troubleshooting

12) Reference queries you will use a lot

Summary

Final thoughts

1) OPENROWSET / OPENDATASOURCE (Ad-hoc distributed queries)

2) SQL Agent PowerShell step (no SQL engine cross-server features)

3) SQLCMD + file drop + BULK INSERT

4) PolyBase / External Table over ODBC (SQL 2019+)

Which one should you choose?

How to plug this into your existing blog/runbook

Security & reliability tips (quick)

Share this:

Like this:

Related

Discover more from SQLYARD

Related Posts

Leave a Comment Cancel Reply

1) `OPENROWSET` / `OPENDATASOURCE` (Ad-hoc distributed queries)

3) `SQLCMD` + file drop + `BULK INSERT`