SQLWebpedia

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.

/*************************************************************************************************
**
**  File: prc_DBA_ReplicationStalenessReport
**
**  Desc: Create HTML email report of all subscriptions that are greater than @UndelivCmdsInDistDB rows behind publisher.
**  
**
**  Created by: Chuck Lathrope 1-2-2010
**  Altered 11/1/2010  Chuck Lathrope  Utilized MSdistribution_status table
*************************************************************************************************/

ALTER PROCEDURE dbo.prc_DBA_ReplicationStalenessReport
   @UndelivCmdsInDistDB  INT = 1000,
   @NotificationEmailAddress VARCHAR(100) = 'dbops@localhost'
AS
BEGIN

SET NOCOUNT ON;

--Put this as a job step on your distributor.
DECLARE @tableHTML  NVARCHAR(MAX), @subjectMsg VARCHAR(150), @HighPriority bit

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'
        WHEN mdh.runstatus = '0' AND mda.subscription_type = '0' THEN 'PushPublication'
       END, '',
   td = CONVERT(VARCHAR(25),mdh.[time]), '',
   td = LEFT(mda.name,LEN(mda.name)-CHARINDEX('-',REVERSE(mda.name),1)), '',
   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 MAX(time) MaxTimeValue, name
       FROM distribution.dbo.msdistribution_agents a
       JOIN distribution.dbo.MSdistribution_history h ON h.agent_id=a.id
       GROUP BY name) x ON x.MaxTimeValue = mdh.Time AND x.name = mda.name
   JOIN (      
           SELECT st.agent_id, SUM(st.UndelivCmdsInDistDB) AS UndelivCmdsInDistDB
           FROM distribution.dbo.MSdistribution_status st
           GROUP BY st.agent_id
       ) und ON mda.id = und.agent_id
   WHERE UndelivCmdsInDistDB > @UndelivCmdsInDistDB
       FOR XML PATH('tr')
) AS NVARCHAR(MAX) ) +    
    N'</table></HTML>'

IF @tableHTML LIKE '%Fail%'
   SET @HighPriority = 1

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

END --Proc creation.

GO