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

Tags

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

2 (4)

You rated this post out of 5. Change rating