Technical Article

Replication Monitor

,

Logreader agent will insert error into distribution..MSrepl_errors.
we can set this procedure into Monitor. which will call this sp for every five minute.
when result set : 0 then Normal, when 1 Then error found in replication.
once record inserted in DBA_Replication_Error_Log then
Alert will displayed in monitor and we need to truncate DBA_Replication_Error_Log table.
Procedure:
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @status BIT = 0

INSERT INTO DBA_Replication_Error_Log
SELECT * 
FROM distribution..MSrepl_errors 
WHERE time BETWEEN (DATEADD(MINUTE,-4,GETDATE())) AND GETDATE()

IF EXISTS(SELECT 1 FROM DBA_Replication_Error_Log)
BEGIN
SET @status = 1
END

SELECT @status AS status
END

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

2 (4)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

2 (4)

You rated this post out of 5. Change rating