Transactional Replication Distribution Delivery Status:
SELECT
@@SERVERNAME As Srv,
mrm.Publisher,
mrm.Publication,
mrm.Agent_Name,
mrm.Publisher_DB,
msa.Article,
mds.UndelivCmdsInDistDB,
CASE WHEN mrm.[status] = 1 THEN 'Started'
WHEN mrm.[status] = 2 THEN 'Succeeded'
WHEN mrm.[status] = 3 THEN 'In Progress'
WHEN mrm.[status] = 4 THEN 'Idle'
WHEN mrm.[status] = 5 THEN 'Retrying'
WHEN mrm.[status] = 6 THEN 'Failed'
ELSE 'Other'
END [Status],
CASE WHEN mrm.warning = 0 THEN 'OK'
WHEN mrm.warning = 1 THEN 'Expired'
WHEN mrm.warning = 2 THEN 'Latency'
ELSE 'OTHER'
END [Warnings],
mrm.cur_latency / 60.0 AS [Latency(Min)]
FROM Distribution.dbo.MSdistribution_status mds WITH(NOLOCK)
INNER JOIN Distribution.dbo.MSarticles msa WITH(NOLOCK) ON msa.article_id = mds.article_id
INNER JOIN Distribution.dbo.MSreplication_monitordata mrm WITH(NOLOCK) ON mrm.agent_id = mds.agent_id
WHERE
mds.UndelivCmdsInDistDB > 0
--AND (mrm.cur_latency / 60.0) > 5
ORDER BY mrm.cur_latency DESC
GO
SELECT * FROM MSdistribution_agents
GO
SELECT
agent_id
, SUM(UndelivCmdsInDistDB) AS [pending_transaction]
FROM
Distribution..MSdistribution_status s WITH ( NOLOCK )
WHERE
agent_id IN (11,12,13,14,15) --modify
GROUP BY
agent_id
GO
|
|
|
|
|
|