Monitoring Replication using Scripts(Version 1.0)

This script can be used to monitor replication using scripts instead of logging into the server and using replication monitor. It gives information on Transactional replication status.

DECLARE @srvname VARCHAR(100)
DECLARE @pub_db VARCHAR(100)
DECLARE @pubname VARCHAR(100)
CREATE TABLE #replmonitor(status    INT NULL,warning    INT NULL,subscriber    sysname NULL,subscriber_db    sysname NULL,publisher_db    sysname NULL,
publication    sysname NULL,publication_type    INT NULL,subtype    INT NULL,latency    INT NULL,latencythreshold    INT NULL,agentnotrunning    INT NULL,
agentnotrunningthreshold    INT NULL,timetoexpiration    INT NULL,expirationthreshold    INT NULL,last_distsync    DATETIME,
distribution_agentname    sysname NULL,mergeagentname    sysname NULL,mergesubscriptionfriendlyname    sysname NULL,mergeagentlocation    sysname NULL,
mergeconnectiontype    INT NULL,mergePerformance    INT NULL,mergerunspeed    FLOAT,mergerunduration    INT NULL,monitorranking    INT NULL,
distributionagentjobid    BINARY(16),mergeagentjobid    BINARY(16),distributionagentid    INT NULL,distributionagentprofileid    INT NULL,
mergeagentid    INT NULL,mergeagentprofileid    INT NULL,logreaderagentname VARCHAR(100))
SELECT b.srvname,a.publisher_db,a.publication
FROM distribution.dbo.MSpublications a,  master.dbo.sysservers b
WHERE a.publisher_id=b.srvid
OPEN replmonitor 
FETCH NEXT FROM replmonitor INTO @srvname,@pub_db,@pubname
INSERT INTO #replmonitor
EXEC distribution.dbo.sp_replmonitorhelpsubscription  @publisher = @srvname
     , @publisher_db = @pub_db
     ,  @publication = @pubname
     , @publication_type = 0
FETCH NEXT FROM replmonitor INTO @srvname,@pub_db,@pubname
CLOSE replmonitor
DEALLOCATE replmonitor

SELECT publication,publisher_db,subscriber,subscriber_db,
        CASE publication_type WHEN 0 THEN 'Transactional publication'
            WHEN 1 THEN 'Snapshot publication'
            WHEN 2 THEN 'Merge publication'
            ELSE 'Not Known' END,
        CASE subtype WHEN 0 THEN 'Push'
            WHEN 1 THEN 'Pull'
            WHEN 2 THEN 'Anonymous'
            ELSE 'Not Known' END,
        CASE status WHEN 1 THEN 'Started'
            WHEN 2 THEN 'Succeeded'
            WHEN 3 THEN 'In progress'
            WHEN 4 THEN 'Idle'
            WHEN 5 THEN 'Retrying'
            WHEN 6 THEN 'Failed'
            ELSE 'Not Known' END,
        CASE warning WHEN 0 THEN 'No Issues in Replication' ELSE 'Check Replication' END,
        latency, latencythreshold, 
        'LatencyStatus'= CASE WHEN (latency > latencythreshold) THEN 'High Latency'
        ELSE 'No Latency' END,
        distribution_agentname,'DistributorStatus'= CASE WHEN (DATEDIFF(hh,last_distsync,GETDATE())>1) THEN 'Distributor has not executed more than n hour'
        ELSE 'Distributor running fine' END
        FROM #replmonitor
DROP TABLE #replmonitor

Leave a Reply

Join the Conversation


  1. This script cannot work; you can’t do an select exec when the proc being called contains another proc call.

  2. The error is being caused by inserts in a stored procedure called internally by sp_replmonitorhelpsubscrion. The work around is to open sp_replmonitorhelpsubscrion using OPENROWSET. Unfortunately, that will also prohibit the use of variables, rendering the point moot for the above script. The only way for this to work would be to use OPENROWSET and ditch the loop.

Leave a comment

Your email address will not be published. Required fields are marked *



Related Posts

Dropping a Subscriber from AG Publisher
Today one of my colleague reached out to me for an help to drop a subscriber from an publisher which is participating in Always On. We seen same...
Performance View on INNER JOIN and OUTER JOIN
Hope you guys are getting ready for Diwali Festival :-). Before you guys start with the festival event I thought of writing an article on perform...
Synonyms - SQL Server
I was working in a project where we are using database mirroring as high availability solution. In the same instance we have multiple databases i...
powered by RelatedPosts