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.
5 comments:
If the transaction sequence number isn't being displayed on the error in the replication monitor, is there another way to find the number or the problematic command? Also, can I configure SQL to capture and display the transaction sequence number for the future - this is a regular problem!
Thanks
A.
Hi Sandesh, Your post did help me a lot but at my end when I fix one table, problem jumps to next and so forth.I have about 1500 tables in replication.
I am enabling replication via backup.
If you could guide any solution for this that will be a great help.
My windows server is 2008r2 and sql server is 2008 R2.
Thanks for your help
Hi Rishi Kumar, please get in touch with me via email. sandeshsegu@sanssql.com
Hello I got the above error and did same as suggested but when I ran below command
exec sp_browsereplcmds @xact_seqno_start = '0x0000003900000018002300000000',
@xact_seqno_end = '0x0000003900000018002300000000', @Command_id=6, @publisher_database_id=6
Nothing was displayed.
Aivivu đại lý vé máy bay, tham khảo
vé máy bay đi Mỹ giá bao nhiêu
vietnam airlines quốc tế
bay nhật bản việt nam
khi nào có chuyến bay từ đức về việt nam
chuyến bay thương mại từ canada về việt nam
ve may bay tu han quoc ve viet nam
khách sạn cách ly
vé máy bay chuyên gia nước ngoài sang Việt Nam
Post a Comment