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.
Updated for SQLSaturday #43 conference talk:
/*************************************************************************************************
**
** File: prc_DBA_HourlyReplicationErrors
**
** Desc: Find latest replication errors and send email.
**
**
** Created by: Chuck Lathrope
** Creation Date: 1-2-2010
*************************************************************************************************/
ALTER PROCEDURE [dbo].[prc_DBA_HourlyReplicationErrors]
AS
BEGIN
SET NOCOUNT ON;
--Put this as a job step on your distributor.
DECLARE @tableHTML NVARCHAR(MAX)
DECLARE @subjectMsg VARCHAR(150)
SELECT @subjectMsg = 'Hourly Replication Errors Reported on ' + CONVERT(VARCHAR(25),GETDATE()) + ' by server ' + @@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)-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.
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)) 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></HTML>' ;
IF @tableHTML IS NOT NULL
EXEC prc_internalsendmail
@HighPriority =0, @address = 'itdatabaseoperations@demandmedia.com',
@subject=@subjectMsg, @body=@tableHTML,@HTML=1
END --Proc creation.
