Article wise Pending Transaction (Transactional Replication)

While troubleshooting transactional replication issues , DBA’s always look for script which can give which article’s transaction are waiting to be replicated and how much

The script below will answer the question

Script

select count(*) as [# of commands] , f.publication, 
     c.publisher_database_id, d.publisher_db, c.article_id, 
     e.article, entry_time 
from MSrepl_commands  c with (NOLOCK) 
inner join MSrepl_transactions  t  with (NOLOCK) on c.publisher_database_id = t.publisher_database_id 
and c.xact_seqno = t.xact_seqno 
inner join MSpublisher_databases d  with (NOLOCK) on d.id = c.publisher_database_id 
inner join MSarticles e  with (NOLOCK) on e.publisher_db = d.publisher_db and e.article_id = c.article_id 
inner join MSpublications f  with (NOLOCK) on f.publisher_db = e.publisher_db and e.publication_id = f.publication_id 
--where f.publication = '(publication_name)' 
group by    c.publisher_database_id, f.publication, d.publisher_db, 
      c.article_id, e.article, entry_time 
order by [# of commands] desc

image

Leave a Reply

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