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

Replication Architecture

Replication Architecture

Replication Troubleshooting

Basics of Replication Troubleshooting

There are many scenarios where you have been alerted for the replication failure and you have to troubleshoot the issue. In this article I will guide you what should be your approach to get the detailed error message and transaction details in replication.

First check the replication monitor and click on the failed publisher. Next step is double click on the failed subscriber from All Subscriptions list.

Now next step is click on the error and check its description.
Error :
Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x0000044100002D93000100000000, Command ID: 1)

From the above error message we have to identify which command is failed to execute on the subscriber.

To get the exact command, find out the distributer server and distribution database for the failed publisher.

Once you get the distribution database server, execute the below query against the distribution DB.

1 2 3 4 5

use distribution go SELECT * FROM msrepl_commands WHERE xact_seqno = 0x0000044100002D93000100000000 AND command_id = 1

Once you execute the above query against the distribution database, you will get the more information about the error, for example Publisher database ID, Article ID and much more…

We have to use the above details, to get the exact command using eitherSP_BROWSEREPLCMDS (If CLR is enabled) or you can cast the command column inmsrepl_commands table.

We will check both the alternatives.

Using SP_BROWSEREPLCMDS 
Please note CLR must be enabled for to use this procedure.

1 2 3 4 5 6

EXEC SP_BROWSEREPLCMDS @xact_seqno_start = '0x0000044100002D930001', @xact_seqno_end = '0x0000044100002D930001', @publisher_database_id = 1033, @article_id = 12, @command_id= 1

By casting command column in msrepl_commands table

Please note if you want to see the better output use the Result to Text as output in SSMS (CTRL + T)

1 2 3 4

SELECT CAST(SUBSTRING(command, 7, 8000) AS NVARCHAR(MAX)) FROM msrepl_commands WHERE xact_seqno = 0x0000044100002D930001 AND command_id = 1

Now you got the exact SQL Command. As a next step check the objects from both the publisher and the subscriber to see the violation of the keys or do the data comparisons etc.

=================================================================================

Row Not Found at the Subscriber - Replication Issue

 

When you find an issue in replication with the error “The row was not found at the Subscriber when applying the replicated command.”, first we have to get the Transaction sequence number and Command ID from the error.

This can be found at Distributer to Subscriber history in replication monitor.

Once we get the Transaction Sequence Number and Command ID we can easily drill down to the command which is causing the issue by using sp_browsereplcmds. Before to this, we have to also find out publisher_database_id.

For finding publisher_database_id, we need to make use of Transaction Sequence Number and Command ID.

Query to find publisher_database_id using Transaction Sequence Number and Command ID

select * from msrepl_commands where xact_seqno = 0x000BF8FB0003411E000400000000 and command_id=6

Once we get the publisher_database_id from the above query, then we need to execute the below query to get the command which is causing the error.

Query to find the command which is causing error

exec sp_browsereplcmds @xact_seqno_start = '0x000BF8FB0003411E000400000000', @xact_seqno_end = '0x000BF8FB0003411E000400000000', @Command_id=6,@publisher_database_id=60

Once we get the command, we can manually sync the missing data from publisher to subscriber to make the replication work fine as before.

Note: All these commands have to be run on distribution database.

This article is also available in pdf format for downloading.

Please Click here to get your copy.

=======================================================