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.

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 either SP_BROWSEREPLCMDS (If CLR is enabled) or you can cast the command column in msrepl_commands table.

We will check both the alternatives.

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

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)

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.

Advertisements

One thought on “Basics of Replication Troubleshooting

Thanks for the comment, will get back to you soon... Jugal Shah

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