Home > SQL Server Replication > Transactions waiting to be replicated to Subscription Database (Transactional Replication)

Transactions waiting to be replicated to Subscription Database (Transactional Replication)

While troubleshooting Replication issues it’s always a question till what point transaction has been replicated to subscription and from which point transaction are in distribution database waiting to be replicated to subscription database.

Replication Architecture


Let’s say distributor agent failed to replicate transaction from distributor to subscriber due to some reason and now after restarting the distributor agent it’s still not replicating the transactions.
There could be n number of reason due to which distributor agent may fail.

In this post we will focus on how to get last transaction replicated to subscriber database and what all transaction are waiting in the distributor database to be replicated.

Every time distributor agent replicates the transaction from distributor to subscriber, it does an entry in a system table dbo.MSreplication_subscriptions which exists in subscriber database.
The MSreplication_subscriptions table contains one row of replication information for each Distribution Agent.

Code: SELECT publisher,publisher_db,publication,transaction_timestamp
FROM dbo.MSreplication_subscriptions


Transaction_timestamp value is same as xact_seqno and till this sequence number all the transaction are replicated to subscription database.

In case u does not have access to the subscription database and to find out the last replicated xact_seqno to subscriber database from distributor database

sp_MSget_last_transaction Publicationid,Publication_database
sp_MSget_last_transaction 2,TestDB

Connect to the Distributor

Code: select * from MSrepl_commands Where xact_seqno>Transaction_timestamp value

This will give you those records which are in distributor and not been replicated to subscriber database after the last replicated xact_seqno
in subscriber database (dbo.MSreplication_subscriptions)

sp_browsereplcmds : @xact_seqno_start, @xact_seqno_end

The stored procedure will use to view pending commands in the distributor database.|
It will display the commands in the readable format.



 

Replication monitor will also give the xact_seqno at which distributor agent failed


 

You can also check the error on browsing MSrepl_errors table in distribution database.

select
*
From MSrepl_errors

 

In this case error is :”The row was not found at the Subscriber when applying the replicated command:

To resolve the same I have xact_seqno and command id , I simple deleted the record from the MSrepl_commands at the distributor.Once done distributor agent will start replicating pending transaction to subscriber.

DELETE from MSrepl_commands where command_id=1295 and xact_seqno=0x000260AD000000100021

  1. k.fyros
    March 9, 2012 at 11:04 am

    and what do you do with consistency errors on the distributor after you deleted the record ?

    • msufian
      November 8, 2012 at 2:18 pm

      Consistency error will not occure in this case.

  2. Praveen
    May 10, 2012 at 7:23 pm

    Very helpful information. Thank you.

  3. December 20, 2012 at 6:06 pm

    Thanks a lot for the very useful information. It helped me a lot.

  4. Tim
    March 30, 2015 at 9:25 pm

    We use Replication for a near realtime dwh. I was looking for a way to see if transactions were committed to the subscription after the lastloadtime to push these changes to the dwh. Comparing if the time in dbo.MSreplication_subscriptions is later than lastloadtime was the answer. Thanks!

  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: