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. Referenced prc_internalsendmail proc can be found on this site at: http://www.sqlwebpedia.com/content/automated-mail-profile-creation-and-send-mail-procedure

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.
**
**
**  Created 1-2-2010 Chuck Lathrope
**  Altered
**  11/01/2010  Chuck Lathrope Utilized MSdistribution_status table
**  06/23/2011  Chuck Lathrope Added Severe Latency status and NOC escalation.
**  10/31/2011  Chuck Lathrope Bug fix with parameter length and like statement.
**  05/03/2012  Chuck Lathrope Added query hint to help greatly.
*************************************************************************************************/

ALTER PROCEDURE dbo.prc_DBA_ReplicationStalenessReport
@UndelivCmdsInDistDB  INT = 2500,
@NotificationEmailAddress VARCHAR(150) = 'yourteamsalertdistributionlist@yourcompany.com'
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), @HighPriority bit

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
    WHEN und.UndelivCmdsInDistDB > 10000 AND mda.NAME NOT LIKE '%DEV%' THEN 'Severe Latency!'
    WHEN und.UndelivCmdsInDistDB > 500000 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'--This is designed for Pull subscriptions.
   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 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><br />
Escalation notes:<br />
Severe Latency = Undelivered Cmds > Latency Limit. If numbers continue to increase over subsequent alert emails,
call on-call DBA to investigate.<br />
</HTML>' OPTION (Force ORDER)

IF @tableHTMML LIKE '%Fail%' OR @tableHTML LIKE '%Severe Latency!%'
BEGIN
  SET @HighPriority = 1
  --Add NOC  team to email delivery
  SET @NotificationEmailAddress = @NotificationEmailAddress + ';noc@yourcompany.com'
END

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

END --Proc creation.

GO