IF @mail = 'Y' BEGIN DECLARE @msg VARCHAR(MAX) = 'Replication on ' + @@SERVERNAME + ' may be experiencing some problems. Attempts to restart the distribution agent have been made. ' + 'If this is not the first message like this that you have received within the last hour, please investigate.' DECLARE @body NVARCHAR(MAX) DECLARE @xml1 NVARCHAR(MAX) DECLARE @tab1 NVARCHAR(MAX) DECLARE @xml2 NVARCHAR(MAX) DECLARE @tab2 NVARCHAR(MAX) SET @xml1 = CAST(( SELECT subscriber AS 'td','',subscriber_db AS 'td','', latency AS 'td','', PendingCmdCount AS 'td','', EstimatedProcessTime AS 'td' FROM ##SubscriptionInfo s FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) SET @tab1 ='

Subscription Information

' -- this command gives us the last 10 measurements of latency for each subscriber SET @xml2 = CAST(( SELECT s.Subscriber_db AS 'td','', s.Records_In_Que AS 'td','', s.CatchUpTime AS 'td','', CONVERT(CHAR(22),LogDate, 100) AS 'td' FROM (SELECT ROW_NUMBER() OVER ( PARTITION BY subscriber_db ORDER BY Logdate DESC ) AS 'RowNumber', subscriber_db , Records_In_Que , CatchUpTime , Logdate FROM DBA.dbo.Replication_Que_History) s WHERE RowNumber <= 8 FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) SET @tab2 ='

Historical Latency Information

Subscriber Subscriber Database Latency(seconds) Undistributed Commands Estimated Catch Up Time
' SET @body = @msg + @tab1 + @xml1 + '
Subscriber Undistributed Commands Catch Up Time Date\Time
' + @tab2 + @xml2 + '' DECLARE @to NVARCHAR(200) SELECT @to = '' -- INSERT YOUR EMAIL ADDRESS HERE EXEC msdb.dbo.sp_send_dbmail @body = @body, @body_format ='HTML', @recipients = @to, @subject = 'Possible Replication Problem' ; END DROP TABLE #PublisherInfo DROP TABLE ##PublicationInfo DROP TABLE ##SubscriptionInfo