How To Monitor Replication in SQL Server

How To Monitor Replication in SQL Server

 how to monitor replication in sql server

This post takes you through the T-SQL script How to Monitor Replication in SQL Server. Usually we need to monitor replication periodically through out the day. I have designed a solution to monitor replication form T-SQL. It’ll send a mail to DBA team that replication is failing. Create the below two objects (Table and PROC) and create a job which calls the stored procedure for every 5 minutes.
This will save us a lot of time and effort for sure

Now here is the script to automate replication monitoring in sql server

Create a Table and a stored procedure. Please have a look at procedure code, all sections are provided with required comments.

Table Creation

 

Stored Procedure Creation:

 

 

Here is the script file: monitor_replication

We can schedule a job to execute this stored procedure.

Posted in High Availability, SQL Scripts | Tagged , , , , , , , , , | 3 Comments

Leave a Reply

3 Comments on "How To Monitor Replication in SQL Server"

Notify of
avatar
Sort by:   newest | oldest | most voted
Minh Dang Van
Guest

Hj all,

Can you help me. I using SQL SERVER 2008, i try to run script “Script to Automate The Replication Monitoring” but that have some error “Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80
An INSERT EXEC statement cannot be nested”. This is my email: dangvanminh89@gmail.com

Thanks for all.

haritha
Guest

Could you please send me the working store procedure
i am having issues with below in the proc
IF(@C>0)

BEGIN

DECLARE @String varchar(8000)

 

SET @String= ‘Replication is Failing Publisher_Name for below Subscribers+CHAR(13)’

 

SET @String=@String+WEB++CAPP+char(13)

 

SELECT @String =@String+subscriber+ +publication+char(13)

FROM repmonitor WHEREStatus = 6

wpDiscuz