Rajanand Ilangovan
Rajanand Ilangovan

Rajanand Ilangovan

How to find which command has failed in SQL Server Replication?

Why command has caused an issue in replication?

Rajanand Ilangovan's photo
Rajanand Ilangovan
·May 14, 2022·

1 min read

Subscribe to my newsletter and never miss my upcoming articles

Listen to this article

To find out the list of replicated transaction, query the MSrepl_transactions table in distribution database. Copy the sequence number of the transaction (i.e., xact_seqno ) which entered the distribution database last.

SELECT top 1 
entry_time AS trans_entry_time_in_dist_db,
publisher_database_id,
xact_id,
xact_seqno
FROM distribution.dbo.MSrepl_transactions
ORDER BY entry_time DESC

Copy the xact_seqno from previous query and replace it in @xact_seqno_start below.

EXEC distribution.dbo.sp_browsereplcmds 
@xact_seqno_start = '0x0005B68700176AEC000C' 
--replace the xact_seqno from the previous query

Sample output: image.png

You will be able to see the command that failed and debug further.

If you just want to see the error message or other queries that are useful to monitor and debug the replication issues, you can refer this blog post.