SQL Replication Error Summary

Your rating: None Average: 4.5 (2 votes)

This proc will monitor for errors so you don't have to monitor Replication Monitor (Replmon) constantly, nor are you forced to use the inflexible builtin SQL Agent Job alerting. Schedule it as a job on your distributor box and it will email you only when there are errors. It will provide a code segment for you to run to see all the errors if there were multiple lines of errors, which is common, but the comments column should be enough to figure it out or at least point out that there is a problem. The Replmon will retry and everything will look good, and only temporarily show a red x, this script will help you from missing the errors before it is too late. I removed my referenc to my handy prc_internalsendmail procedure that helps automating profile creation and sending mail to make this script more generic. Also, check out my other good replication proc for monitoring replication latency that could capture unknown issues: http://www.sqlwebpedia.com/content/sql-replication-undelivered-command-count

 

USE DBOPS
GO

IF (OBJECT_ID('dbo.prc_DBA_HourlyReplicationErrors') IS NULL)
BEGIN
   EXEC
('create procedure dbo.prc_DBA_HourlyReplicationErrors  as raiserror(''Empty Stored Procedure!!'', 16, 1) with seterror')

  
IF (@@error = 0)
      
PRINT 'Successfully created empty stored procedure dbo.prc_DBA_HourlyReplicationErrors.'
  
ELSE
   BEGIN
       PRINT
'FAILED to create stored procedure dbo.prc_DBA_HourlyReplicationErrors.'
  
END
END
GO

PRINT 'Altering Procedure: dbo.prc_DBA_HourlyReplicationErrors'
GO

/*************************************************************************************************
**
**  File: prc_DBA_HourlyReplicationErrors
**
** Desc: Find latest replication errors and send email.
** 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 by: Chuck Lathrope 1-2-2010
**  11/1/2010  Chuck Lathrope  Added more error tables to capture non-errors, but true issues.
**  11/23/2010  Chuck Lathrope  Bug fix for sections that have 0 rows becomes a NULL in email.
**  9/4/2012   Chuck Lathrope  Added AND mda.subscriber_id > 0 to remove "virtual" subscribers that SQL engine uses.
**  11/21/2012  Chuck Lathrope  Removed date restriction from job status check table results.
**  11/26/2012 Chuck Lathrope  Added exception for RunStatus = 0 which is for Immediate_Sync subscriptions
**  12/28/2013 Chuck Lathrope  Minor changes/bug fixes and note additions
*************************************************************************************************/
ALTER PROCEDURE dbo.prc_DBA_HourlyReplicationErrors
  
@NotificationEmailAddress VARCHAR(200) = 'DBOpsTeamAlias@localhost' --Insert real distribution list here.
AS
BEGIN
   SET NOCOUNT ON
;
  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

  
DECLARE @TableHTML NVARCHAR(MAX)
       ,
@SubjectMsg VARCHAR(150)
       ,
@ErrorText VARCHAR(MAX)
       ,
@BadJobStatus VARCHAR(MAX)
       ,
@OrphanedErrors VARCHAR(MAX)
       ,
@PushedPublications VARCHAR(MAX)

  
SELECT @SubjectMsg = 'Hourly Replication Errors Reported on ' + CONVERT(VARCHAR(25), GETDATE()) + ' by server ' + @@servername

  
SET @ErrorText = N'<HTML><H2>' + @SubjectMsg + '</H2>' + N'<table border="1" cellpadding="0" cellspacing="2">'
      
+ N'<th>PublisherDB-Subscriber</th><th>subscriber_db</th><th>StatusDesc</th><th>LastSynchronized</th><th>Comments</th><th>Query to get more info</th></tr>'
      
+ CAST((
          
--See all errors in table form:
          
SELECT td = mda.Name,''
              
,td = mda.subscriber_db,''
              
,td = CASE
                
WHEN mdh.runstatus = 1 THEN 'Start'  
                
WHEN mdh.runstatus = 2 THEN 'Succeed/Stopped'  
                
WHEN mdh.runstatus = 3 THEN 'InProgress'  
                
WHEN mdh.runstatus = 4 THEN 'Idle'  
                
WHEN mdh.runstatus = 5 THEN 'Retry'  
                
WHEN mdh.runstatus = 6 THEN 'Failure'  
                
END,''
              
,td = mdh.TIME,''
              
,td = mdh.comments,''
              
,td = 'SELECT * FROM Distribution.dbo.msrepl_errors WITH (NOLOCK) WHERE id = ' + CAST(mdh.error_id AS VARCHAR(8))
          
FROM Distribution.dbo.MSdistribution_agents mda
          
JOIN Distribution.dbo.MSdistribution_history mdh ON mdh.agent_id = mda.id
          
JOIN (
              
SELECT agent_id
                  
,MAX(error_id) AS MaxError_id
              
FROM Distribution.dbo.MSdistribution_history mdh
              
WHERE start_time > DATEADD(hh, - 1, GETDATE())
              
GROUP BY agent_id
              
) AS MaxErrorID ON MaxErrorID.agent_id = mda.id
                  
AND MaxErrorID.MaxError_id = mdh.error_id
          
WHERE start_time > DATEADD(hh, - 1, GETDATE())
               AND
error_id <> 0
          
FOR XML PATH('tr')
       )
AS NVARCHAR(MAX)) + N'</table><br />'

  
-- Add another table that shows all the recent errors that can contain errors from orphaned agent jobs.
  
SET @OrphanedErrors = N'<table border="0" cellpadding="0" cellspacing="2">'
      
+ N'<th><H3>Last Hours Logged Replication Errors</H3></th></tr>'
      
+ CAST((
              
SELECT DISTINCT CAST(error_text AS VARCHAR(200)) AS td
              
FROM Distribution.dbo.msrepl_errors
               WWHERE [Time]
> DATEADD(hh, - 1, GETDATE())
                   AND
source_type_id <> 1 --Not very helpful typically. Does show path.
              
FOR XML PATH('tr')
           )
AS NVARCHAR(MAX)) + N'</table><br />'

  
-- Add another table that shows all the jobs that are in a bad job state.
  
SET @BadJobStatus = N'<table border="0" cellpadding="0" cellspacing="2">'
      
+ N'<th>Agent Name</th><th>History Comment</th><th>Job Status</th><th>Time Recorded</th></tr>'
      
+ CAST((
              
SELECT td = a.Name,''
                  
,td = Comments,''
                  
,td = CASE
                      
WHEN runstatus = 1 THEN 'Start'
                      
WHEN runstatus = 2 THEN 'Succeed/Stopped'
                      
WHEN runstatus = 3 THEN 'InProgress'
                      
WHEN runstatus = 4 THEN 'Idle'
                      
WHEN runstatus = 5 THEN 'Retry'
                      
WHEN runstatus = 6 THEN 'Failure'
                      
END,''
                  
,td = [Time]
              
FROM Distribution.dbo.MSdistribution_agents a
              
JOIN Distribution.dbo.MSdistribution_history h ON h.agent_id = a.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 = h.[Time]
                  
AND x.Name = a.Name
              
WHERE runstatus NOT IN (0,1,3,4) --Assuming continuous replication is desired, else add 2 here.
              
FOR XML PATH('tr')
           )
AS NVARCHAR(MAX)) + N'</table><br />'

  
--Find push publications (For small environments (e.g. < ~10 subscribers), push is fine, just comment out).
  
SET @PushedPublications = N'<table border="0" cellpadding="0" cellspacing="2">'
      
+ N'<th>Agent Name</th><th>Subscription Type (Should only be Pull)</th><th>Date Created</th></tr>'
      
+ CAST((
              
SELECT td = mda.NAME,''
                  
,td = CASE
                      
WHEN mda.subscription_type = 0 THEN 'Push'
                      
WHEN mda.subscription_type = 2 THEN 'Anonymous'
                      
END,''
                  
,td = creation_date
              
FROM Distribution.dbo.MSdistribution_agents mda
              
WHERE mda.subscription_type <> 1 --0 = Push. 1 = Pull. 2 = Anonymous.
                  
AND mda.subscriber_id > 0 --0 is for Virtual subscribers used in Immediate_Sync = on and/or Anonymous subs
              
FOR XML PATH('tr')
           )
AS NVARCHAR(MAX)) + N'</table></HTML>'
  
  
SET @TableHTML = ISNULL(@ErrorText, '') + ISNULL(@BadJobStatus, '') + ISNULL(@OrphanedErrors, '') + ISNULL(@PushedPublications, '')

  
IF @TableHTML IS NOT NULL AND @TableHTML <> ''
      
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'

END --Proc creation.
GO

AttachmentSize
prc_DBA_HourlyReplicationErrors.sql6.49 KB