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"

newest oldest most voted
Notify of
Minh Dang Van

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:

Thanks for all.


Could you please send me the working store procedure
i am having issues with below in the proc


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