SQL Replication Error Summary
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 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. Referenced in the proc is my prc_internalsendmail procedure that helps automating profile creation and sending mail. 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.
**
**
** 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 comments said: Distribution agent for subscription added.
** 12/14/2012 Chuck Lathrope Removed where clause in inner subquery for MSdistribution_history to get latest info from all jobs.
*************************************************************************************************/
ALTER PROCEDURE dbo.prc_DBA_HourlyReplicationErrors
@NotificationEmailAddress VARCHAR(100) = 'YourDistributionListforAlerts@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), @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 from today in table form:
SELECT td = REPLACE(LEFT(mda.name,LEN(mda.name)-CHARINDEX('-',REVERSE(name),1)),'VENOMDB1-',''), '',
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 (nolock) where id = ' + CAST(mdh.error_id AS VARCHAR(8))
FROM distribution.dbo.MSdistribution_agents mda (nolock)
JOIN distribution.dbo.MSdistribution_history mdh (nolock) ON mdh.agent_id = mda.id
JOIN (SELECT agent_id, MAX(error_id) AS MaxError_id FROM distribution.dbo.MSdistribution_history mdh (nolock)
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(2000)) AS td
FROM distribution.dbo.msrepl_errors (nolock)
WHERE tiime > 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)
FOR XML PATH('tr')
) AS NVARCHAR(MAX) ) + N'</table><br />'
--Find push publications because for performance in large environment, they should be Pull.
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 mda.name,
CASE
WHEN mda.subscription_type = '0' THEN 'Push'
WHEN mda.subscription_type = '2' THEN 'Anonymous'
END ,
creation_date
FROM distribution.dbo.MSdistribution_agents mda
WHERE mda.subscription_type <> '1'
AND mda.subscriber_id > 0
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 dbops.dbo.prc_internalsendmail
@HighPriority=1, @address=@NotificationEmailAddress,
@subject=@subjectMsg, @body=@tableHTML, @HTML=1
END --Proc creation.
GO
