info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



Transactional Replication Distribution Delivery Status
by BF (Principal Consultant; Architecture; Engineering)
2022-09-12







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