Search
SQL Server Expert Blogs
SQL Replication Error Summary
We had a script that would try to get all the information about replication's current condition, but I felt it was doing too much and didn't do a good enough job. So, I re-wrote it in to two scripts. This script will tell you all the errors that have happened in past hour. Schedule it as a job on your distributor box and it will email you only when there were 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 Replication Monitor in SSMS 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.
/*************************************************************************************************
**
** 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.
*************************************************************************************************/
ALTER PROCEDURE dbo.prc_DBA_HourlyReplicationErrors
@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), @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(200)) AS td
FROM distribution.dbo.msrepl_errors (nolock)
WHERE 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 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
WHERE runstatus NOT IN (3,4)
AND time > DATEADD(hh,-1,GETDATE())
GROUP BY name) x ON x.MaxTimeValue = h.Time AND x.name = a.name
WHERE runstatus NOT IN (1,3,4)
FOR XML PATH('tr')
) AS NVARCHAR(MAX) ) + N'</table><br />'
--Find push publications
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'
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
