You are hereSQL Server 2005 DBA / SQL Replication Error Summary
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.
DECLARE @tableHTML NVARCHAR(MAX)
DECLARE @subjectMsg VARCHAR(150)
SELECT @subjectMsg = 'Hourly Replication Errors Reported' + CONVERT(VARCHAR(25),GETDATE()) + ' ' + @@servername
SET @tableHTML =
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)-3),'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></HTML>' ;
IF @tableHTML IS NOT NULL
EXEC dbops.dbo.prc_internalsendmail
@HighPriority =0, @address = 'youremailaddress@localhost',
@subject=@subjectMsg, @body=@tableHTML,@HTML=1
