Script to get replication Undistributed Commands
Script to Monitor Replication by checking last refresh time and pending commands
SELECT ( CASE WHEN Mdh.Runstatus = '1' THEN 'Start' WHEN Mdh.Runstatus = '2' THEN 'Succeed' WHEN Mdh.Runstatus = '3' THEN 'InProgress' WHEN Mdh.Runstatus = '4' THEN 'Idle' WHEN Mdh.Runstatus = '5' THEN 'Retry' WHEN Mdh.Runstatus = '6' THEN 'Fail' END ) [Run status], Mda.Subscriber_db [Subscriber db], Mda.Publication [Pub name], Mdh.[Time] [Lastsynchronized], Und.Undelivcmdsindistdb [Undistcom], Mdh.Comments [Comments], DATEDIFF(SECOND, Mdh.[Time], GETDATE()) Lastrefresh FROM DISTRIBUTION.DBO.Msdistribution_agents Mda LEFT JOIN DISTRIBUTION.DBO.Msdistribution_history Mdh ON Mdh.Agent_id = Mda.Id JOIN (SELECT S.AGENT_ID, Maxagentvalue.[Time], SUM(CASE WHEN Xact_seqno > Maxagentvalue.Maxseq THEN 1 ELSE 0 END) AS Undelivcmdsindistdb FROM DISTRIBUTION.DBO.Msrepl_commands T (NOLOCK) JOIN DISTRIBUTION.DBO.Mssubscriptions AS S (NOLOCK) ON ( T.ARTICLE_ID = S.ARTICLE_ID AND T.PUBLISHER_DATABASE_ID = S.PUBLISHER_DATABASE_ID ) JOIN (SELECT Hist.AGENT_ID, MAX(Hist.[TIME]) AS [Time], H.Maxseq FROM DISTRIBUTION.DBO.Msdistribution_history Hist ( NOLOCK) JOIN (SELECT Agent_id, ISNULL(MAX(Xact_seqno), 0x0) AS Maxseq FROM DISTRIBUTION.DBO.Msdistribution_history ( NOLOCK) GROUP BY Agent_id) AS H ON ( Hist.AGENT_ID = H.Agent_id AND H.Maxseq = Hist.XACT_SEQNO ) GROUP BY Hist.AGENT_ID, H.Maxseq) AS Maxagentvalue ON Maxagentvalue.Agent_id = S.AGENT_ID GROUP BY S.AGENT_ID, Maxagentvalue.[Time]) Und ON Mda.Id = Und.Agent_id AND Und.[Time] = Mdh.[Time] ORDER BY Mdh.[Time] DESC
Regards,
Nirav Gajjar