使用建立令牌的方式，每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

Sql Server實時監控釋出訂閱複製Replication、subscription有多少延遲的方法

相關文章