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”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.