Labels

Friday, 4 October 2019

Script to find whats going in Distribution or Replication is getting moving or not ?

USE distribution
GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT ma2.publisher_db
 ,mh1.delivery_latency / (1000 * 60) AS delivery_latency_Minutes
 ,mh1.agent_id
 ,mh1.TIME
 ,CAST(mh1.comments AS XML) AS comments
 ,CASE mh1.runstatus WHEN 1 THEN 'Start' 
WHEN 2 THEN 'Succeed.' 
WHEN 3 THEN 'In progress.' 
WHEN 4 THEN 'Idle.' 
WHEN 5 THEN 'Retry.' 
WHEN 6 THEN 'Fail' END AS STATUS
 ,mh1.duration
 ,mh1.xact_seqno
 ,mh1.delivered_transactions
 ,mh1.delivered_commands
 ,mh1.average_commands
 ,mh1.delivery_time
 ,mh1.delivery_rate
 ,ma2.name AS jobname
FROM mslogreader_history mh1
INNER JOIN (
 SELECT mh1.agent_id
  ,MAX(mh1.TIME) AS maxtime
 FROM mslogreader_history mh1
 INNER JOIN MSlogreader_agents ma ON ma.id = mh1.agent_id
 GROUP BY mh1.agent_id
 ) AS mh2 ON mh1.agent_id = mh2.agent_id
 AND mh1.TIME = mh2.maxtime
INNER JOIN MSlogreader_agents ma2 ON ma2.id = mh2.agent_id
ORDER BY mh1.delivery_latency DESC

No comments:

Post a Comment