Find Transactional Replication Details
November 21, 2014
This will be a quick post about a script to find replication metadata in detail. You need to run this in your distributor (where distribution database resides) server and filter in various ways depending on your needs. Also, modify the order based on what you are looking for.
Run where distribution database resides
USE distribution;
GO
SELECT DISTINCT
p.publication ,
p.publisher_db ,
SUBSTRING(da.name, 0, CHARINDEX('-', da.name)) AS Source_Server ,
a.article ,
a.source_object ,
s.subscriber_db ,
REVERSE(SUBSTRING(REVERSE(da.name),
CHARINDEX('-', REVERSE(da.name)) + 1,
(( CHARINDEX('-', REVERSE(da.name),
CHARINDEX('-', REVERSE(da.name)) + 1) )
- ( CHARINDEX('-', REVERSE(da.name)) ) - 1 ))) AS Destination_Server ,
a.destination_object ,
da.name AS DistributionAgentName ,
sa.name AS SnapShotAgentName
FROM
distribution.dbo.MSpublications p
JOIN distribution.dbo.MSarticles a ON a.publisher_id = p.publisher_id
AND a.publication_id = p.publication_id
JOIN distribution.dbo.MSsubscriptions s ON s.publisher_id = p.publisher_id
AND s.publication_id = p.publication_id
AND s.article_id = a.article_id
JOIN distribution.dbo.MSdistribution_agents da ON da.publisher_id = p.publisher_id
AND da.publisher_db = p.publisher_db
AND da.subscriber_db = s.subscriber_db
JOIN distribution.dbo.MSsnapshot_agents sa ON p.publication = sa.publication
ORDER BY
p.publication ,
p.publisher_db ,
a.article ,
a.source_object ,
s.subscriber_db;
2 replies on “Find Transactional Replication Details”
The statement has a typo or incorrect column heading. The report does display the snapshot agent – it displays the distribution agent.
Great site,
Joe O’Brien
Joe Thank you for your comment. Fixed the column header and also added snapshot agent name.