Home > SQL Server Replication > Article wise Pending Transaction (Transactional Replication)

Article wise Pending Transaction (Transactional Replication)

While troubleshooting transactional replication issues , the very common question people ask is for which tables/articles 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

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: