SQL Replication Undelivered Command Count

Your rating: None Average: 5 (1 vote)

Ever wanted to get an email summary of subscriptions that are behind a certain amount? This script can do it! Just setup as a job on your distributor and it will send you a summary email with how many commands are yet to be delivered in a table format for easy reading.

I used to reference prc_internalsendmail proc, but made this script more generic, but you can find the handy mail proc here: http://www.sqlwebpedia.com/content/automated-mail-profile-creation-and-send-mail-procedure. And if you liked this script, make sure to grab this one also: http://www.sqlwebpedia.com/content/sql-replication-error-summary.

 

USE DBOPS
GO
IF (OBJECT_ID('dbo.prc_DBA_ReplicationStalenessReport') IS NULL)
BEGIN
   EXEC
('create procedure dbo.prc_DBA_ReplicationStalenessReport  as raiserror(''Empty Stored Procedure!!'', 16, 1) with seterror')
  
IF (@@error = 0)
      
PRINT 'Successfully created empty stored procedure dbo.prc_DBA_ReplicationStalenessReport.'
  
ELSE
   BEGIN
       PRINT
'FAILED to create stored procedure dbo.prc_DBA_ReplicationStalenessReport.'
  
END
END
GO

PRINT 'Altering Procedure: dbo.prc_DBA_ReplicationStalenessReport'
GO
/*************************************************************************************************
**
**  File: prc_DBA_ReplicationStalenessReport
**
** Desc: Create HTML email report of all subscriptions that are greater than @UndelivCmdsInDistDB rows behind publisher.
**  Install: Put this as a job step on your distributor server.
**     If your distribution database is not named distributor, change code below manually to its name.
**
** Created 1-2-2010    Chuck Lathrope
**  Altered
** 11/1/2010           Chuck Lathrope  Utilized MSdistribution_status table
**  5/3/2012           Chuck Lathrope  Added query hint to help greatly.
** 7/1/2013            Chuck Lathrope  Ignore Virtual subscribers used with immediate_sync = on.
**  12/28/2013         Chuck Lathrope  Added notes on immediate_sync if you want to see its info.
**                                     And type conversion for int status values.
** 1/3/2013            Chuck Lathrope  Perf improvements; Addition of last time sync on dist agent
*************************************************************************************************/

ALTER PROCEDURE dbo.prc_DBA_ReplicationStalenessReport
  
@UndelivCmdsInDistDB  INT = 1000, --Change based on your environment and acceptable latency.
  
@NotificationEmailAddress VARCHAR(200) = 'DBOpsTeamAlias@localhost' --Insert real distribution list here.
AS
BEGIN

   SET NOCOUNT ON
;
  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

  
--Put this as a job step on your distributor.
  
DECLARE @TableHTML NVARCHAR(MAX), @SubjectMsg VARCHAR(150)

  
SELECT @SubjectMsg = 'Half-Hourly Replication Status ' + CONVERT(VARCHAR(25),GETDATE()) + ' ' + @@servername

  
--Final data select statement:
  
SELECT @TableHTML =    
      
N'<H2>' + @SubjectMsg + '</H2>' +
      
N'<table border="1" cellpadding="0" cellspacing="2">' +    
      
N'<tr><th>Status Code</th><th>Last Synchronized</th>' +
      
N'<th>PublisherDB-Subscriber</th><th>Undelivered Cmds</th><th>Subscriber DB</th><th>Subscription Type</th></tr>' +
      
CAST ( ( SELECT
          
td = CASE
          
--This 10000 value is arbitrary for your environment and just to help clue everyone this is a Severe latency message.
            
WHEN und.UndelivCmdsInDistDB > 10000 AND mda.subscription_type > 0 THEN 'Severe Latency!'
            
WHEN mdh.runstatus = 1 THEN 'Start'
            
WHEN mdh.runstatus = 2 THEN 'Succeed'
            
WHEN mdh.runstatus = 3 THEN 'InProgress'
            
WHEN mdh.runstatus = 4 THEN 'Idle'
            
WHEN mdh.runstatus = 5 THEN 'Retry'
            
WHEN mdh.runstatus = 6 THEN 'Fail'
            
WHEN mdh.runstatus = 0 AND mda.subscription_type = 0 THEN 'PushPublication'
          
END, '',
      
td = CONVERT(VARCHAR(25),mdh.[time]), '',
      
td = mda.name, '',
      
td = und.UndelivCmdsInDistDB, '',
      
td = mda.subscriber_db, '',
      
td = CASE
            
WHEN mda.subscription_type = 0 THEN 'Push'
            
WHEN mda.subscription_type = 1 THEN 'Pull'
            
WHEN mda.subscription_type = 2 THEN 'Anonymous'
          
END
       FROM
Distribution.dbo.MSdistribution_agents mda
      
JOIN Distribution.dbo.MSdistribution_history mdh ON mdh.agent_id = mda.id
      
JOIN (
          
SELECT h.agent_id, MAX([time]) MaxTimeValue
          
FROM Distribution.dbo.msdistribution_agents a
          
JOIN Distribution.dbo.MSdistribution_history h ON h.agent_id=a.id
          
WHERE a.subscriber_db <> 'virtual'--we don't care about immediate_sync storage.
               --If you do care, change to this: AND mda.subscriber_id >= -1
          
GROUP BY h.agent_id) x ON x.MaxTimeValue = mdh.time AND x.agent_id = mda.id
      
JOIN (      
              
SELECT st.agent_id, SUM(st.UndelivCmdsInDistDB) AS UnddelivCmdsInDistDB
              
FROM Distribution.dbo.MSdistribution_status st
              
GROUP BY st.agent_id
          
) und ON mda.id = und.agent_id
      
WHERE (UndelivCmdsInDistDB > @UndelivCmdsInDistDB
      
AND mda.subscriber_db <> 'virtual') --we don't care about immediate_sync storage.
           --If you do care, change to this: AND mda.subscriber_id >= -1
      
OR MaxTimeValue < DATEADD(hh,-1,GETDATE()) --Nothing has happened in an hour on distribution agent
          
FOR XML PATH('tr')
   )
AS NVARCHAR(MAX) ) +    
  
N'</table><br />
Escalation notes:<br />
Severe Latency = Undelivered Cmds > Latency Limit.<br />
Last Synchronized time value should be less than an hour.<br />
If numbers continue to increase over subsequent alert emails, call on-call DBA to investigate.<br />
</HTML>'

  
IF @TableHTML LIKE '%Fail%' OR @TableHTML LIKE '%Severe Latency!%'
      
EXEC msdb.dbo.sp_send_dbmail
          
@profile_name = 'SQL_Admin' --It must exist already.
          
,@Importance = 'HIGH'
          
,@recipients = @NotificationEmailAddress
          
,@subject = @SubjectMsg
          
,@body = @TableHTML
          
,@body_format = 'html'
  
ELSE
       EXEC
msdb.dbo.sp_send_dbmail
          
@profile_name = 'SQL_Admin' --It must exist already.
          
,@Importance = 'NORMAL'
          
,@recipients = @NotificationEmailAddress
          
,@subject = @SubjectMsg
          
,@body = @TableHTML
          
,@body_format = 'html'

END --Proc creation.

GO

AttachmentSize
prc_DBA_ReplicationStalenessReport.txt5.24 KB