SQL Server Replication – Find the number of undelivered transactions from a Publisher


The below mentioned script helps us to find the number of undelivered transactions from the publisher.
Courtesy: http://www.sqlsoldier.com/wp/sqlserver/countingunreplicatedcommandsandtransactions

With MaxXact (ServerName, PublisherDBID, XactSeqNo)
 As (Select S.name, DA.publisher_database_id,
 max(H.xact_seqno)
 From distribution.dbo.MSdistribution_history H with(nolock)
 Inner Join distribution.dbo.MSdistribution_agents DA with(nolock)
On DA.id = H.agent_id
 Inner Join master.sys.servers S with(nolock)
 On S.server_id = DA.subscriber_id
 Group By S.name, DA.publisher_database_id)
 Select MX.ServerName, MX.PublisherDBID,
COUNT(*) As TransactionsNotReplicated
 From distribution.dbo.msrepl_transactions T with(nolock)
 Right Join MaxXact MX On
MX.XactSeqNo < T.xact_seqno And
MX.PublisherDBID = T.publisher_database_id
 Group By MX.ServerName, MX.PublisherDBID;
Advertisements

Tagged: , , , ,

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: