Sql Server实时监控发布订阅复制Replication、subscription有多少延迟的方法

使用创建令牌的方式，每15分创建一次令牌，然后过15分钟再查询上一次创建令牌产生的延迟结果，可以使用一个每15分钟运行一次的job来实现，第一步查询上一次创建令牌产生的延迟结果，第二步创建令牌

Job的第一步查询令牌跟踪结果，可以把结果写入一张临时表，结果大于1行，说明有延迟，在图形界面SSMS replication monitor–Tracer Tokens看到publisher to distributor或distributor to subscriber或total latency都是pending状态的话，说明发布订阅正经历延迟，此时dbo.MStracer_tokens表的字段distributor_commit、publisher_commit、subscriber_commit在当前时间点的结果都是null

SELECT dm.publisher_db,dm.publication,
t.publisher_commit,
t.distributor_commit,
h.subscriber_commit,
Datediff(s,t.publisher_commit,t.distributor_commit) as 'dist_diff',
Datediff(s,t.distributor_commit,h.subscriber_commit) as 'subs_diff' 
FROM (SELECT mt.* FROM distribution.dbo.MStracer_tokens mt JOIN 
(SELECT publication_id,MAX(tracer_id) mtid FROM distribution.dbo.MStracer_tokens GROUP BY publication_id) mtgid
on mt.publication_id=mtgid.publication_id AND mt.tracer_id=mtgid.mtid) t
JOIN distribution.dbo.MStracer_history h
ON t.tracer_id = h.parent_tracer_id
JOIN distribution.dbo.MSpublications dm ON t.publication_id=dm.publication_id
where Datediff(s,t.publisher_commit,t.distributor_commit)+Datediff(s,t.distributor_commit,h.subscriber_commit) > 300 
or Datediff(s,t.publisher_commit,t.distributor_commit) is null
or Datediff(s,t.distributor_commit,h.subscriber_commit) is NULL
OR t.distributor_commit IS NULL
OR t.publisher_commit IS NULL
OR h.subscriber_commit IS NULL

Job的第二步创建令牌

DECLARE @cmd VARCHAR(500)

DECLARE cmd_cursor CURSOR FOR 
SELECT publisher_db+'.sys.sp_posttracertoken @publication = '''+publication+'''' FROM distribution.DBO.MSpublications
WHERE publication NOT IN ('ms_hhsfint')

OPEN cmd_cursor  
FETCH NEXT FROM cmd_cursor INTO @cmd  

WHILE @@FETCH_STATUS = 0  
BEGIN  
	PRINT @cmd
	EXEC(@cmd)
      FETCH NEXT FROM cmd_cursor INTO @cmd 
END 

CLOSE cmd_cursor  
DEALLOCATE cmd_cursor