获取同步链agent堵塞的表以及具体条数

--用于详细查看当前实例下，受到堵塞的 表名、 命令行数、影响行数

--获取堵塞的影响行数

SELECT [article_id]
      ,[agent_id]
      ,[UndelivCmdsInDistDB]
      ,[DelivCmdsInDistDB]
  FROM [distribution].[dbo].[MSdistribution_status]
order by UndelivCmdsInDistDB desc

--获得堵塞的命令行数

  SELECT 
      t.article_id
     ,s.agent_id
     ,'UndelivCmdsInDistDB'=SUM(CASE WHEN xact_seqno > h.maxseq THEN 1 ELSE 0 END), 
		'DelivCmdsInDistDB'=SUM(CASE WHEN xact_seqno <=  h.maxseq THEN 1 ELSE 0 END) 
FROM (
	SELECT distinct article_id,publisher_database_id, xact_seqno  
	 FROM MSrepl_commands with (NOLOCK) 
) as t  JOIN
 (
	SELECT distinct agent_id,article_id,publisher_database_id 
	FROM MSsubscriptions with (NOLOCK) 
) AS s   
	ON (t.article_id = s.article_id AND t.publisher_database_id=s.publisher_database_id ) 
 JOIN (
	SELECT agent_id,'maxseq'= isnull(MAX(xact_seqno),0x0) 
	FROM MSdistribution_history WITH (NOLOCK) 
	GROUP BY agent_id
) 
as h  ON (h.agent_id=s.agent_id)  
GROUP BY t.article_id,s.agent_id    

--获取发生堵塞的表名

use distribution
go
SELECT 
    b.publisher_db
   ,b.article
   --,c.UndelivCmdsInDistDB --当前表堵塞影响行数
FROM dbo.MSsubscriptions  AS a with(nolock)
INNER JOIN dbo.MSarticles AS b with(nolock)
    ON a.publisher_id=b.publisher_id 
    AND a.publisher_db=b.publisher_db 
    AND a.article_id=b.article_id 
INNER JOIN [dbo].[MSdistribution_status] AS c with(nolock)
    ON a.agent_id=c.agent_id 
    AND a.article_id=c.article_id 
WHERE 
    a.agent_id=75 
    AND a.article_id=2