SQL Replication Undelivered Command Count

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.

DECLARE @tableHTML  NVARCHAR(MAX) ;    
DECLARE @subjectMsg VARCHAR(150)  
DECLARE @UndelivCmdsInDistDB  INT
SET @UndelivCmdsInDistDB = 30

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

--Final data select statement:
SET @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 
        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'
       END, '',
   td = CONVERT(VARCHAR(25),mdh.[time]), '',
   td = REPLACE(LEFT(mda.name,LEN(mda.name)-3),'VENOMDB1-',''), '',
   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
   LEFT JOIN distribution.dbo.MSdistribution_history mdh ON mdh.agent_id = mda.id
   JOIN (SELECT s.agent_id, MaxAgentValue.[time], SUM(CASE WHEN xact_seqno > MaxAgentValue.maxseq THEN 1 ELSE 0 END) AS UndelivCmdsInDistDB
           FROM distribution.dbo.MSrepl_commands t (NOLOCK) 
           JOIN distribution.dbo.MSsubscriptions AS s (NOLOCK) ON (t.article_id = s.article_id AND t.publisher_database_id=s.publisher_database_id )
           JOIN (   SELECT hist.agent_id, MAX(hist.[time]) AS [time], h.maxseq 
                   FROM distribution.dbo.MSdistribution_history hist (NOLOCK)
                   JOIN (SELECT agent_id,ISNULL(MAX(xact_seqno),0x0) AS maxseq
                           FROM distribution.dbo.MSdistribution_history (NOLOCK) 
                           GROUP BY agent_id) AS h 
                     ON (hist.agent_id=h.agent_id AND h.maxseq=hist.xact_seqno)
                   GROUP BY hist.agent_id, h.maxseq
                   ) AS MaxAgentValue ON MaxAgentValue.agent_id = s.agent_id
           GROUP BY s.agent_id, MaxAgentValue.[time]
       ) und ON mda.id = und.agent_id AND und.[time] = mdh.[time]
   WHERE UndelivCmdsInDistDB > @UndelivCmdsInDistDB
-- order by und.UndelivCmdsInDistDB desc
       FOR XML PATH('tr')
) AS NVARCHAR(MAX) ) +    
    N'</table></HTML>'

IF @tableHTML IS NOT NULL
   EXEC dbops.dbo.prc_internalsendmail 
   @HighPriority =0, @address = 'youremailaddress@localhost', 
   @subject=@subjectMsg, @body=@tableHTML,@HTML=1