Fixing Transactional Replication in SQL Server

SQL Server has a very solid criteria for replication transactions to the subscribers. First it sends out a batch. If it completes then it goes on to the next one. If not, then it tries each of the transactions in the set individually to see if that corrects the problem. Once in a while, even this process fails and you have a hung transaction that keeps the process from completing and, of course, the replication process just doesn’t skip over automagically. So you stuck with troubleshooting…but it doesn’t have to be that bad. This is the process you should follow. First, you need to execute a couple of stored procedures against the distribution database.

sp_helpsubscriptionerrors

@publisher='<publisher_name>’,@publisher_db='<publisher_db>’,

@publication='<publication_name>’,

@subscriber='<subscriber_name>’,@subscriber_db='<subscriber_db>’

This procedure will give you details about the error that occurred. More importantly it will provide you with the sequence no that you will need to get the command that tried to execute …..so that you can continue troubleshooting.

Now that we have found the sequence number we can use the following procedure to get the exact command that was executed.

EXECUTE distribution.dbo.sp_browsereplcmds

@xact_seqno_start = ‘0x000734CA00003C4F000C00000000’,

@xact_seqno_end = ‘0x000734CA00003C4F000C00000000’,

@publisher_database_id = 24, @command_id = 1

In the example above, you can provide either a single sequence no or a range of them. You can get the database id and command id from the previous command. Now you should be able to use the statement to actually execute against the subscriber database. Now you should be able to figure out what went wrong. At the very least, you should be able to perform the intended transaction through a SQL statement using the values. Once you have corrected the issue and applied the transaction or decided to skip it….you need a way to tell the replication process to skip that transaction. You will need the sequence no that you found in the previous statements and will need to issue one last statement before starting up the process again. This time, however, you need to execute it against the subscription database.

sp_setsubscriptionxactseqno  ‘<publisher_name>’,'<publication_db>’,

‘<publication_name>’,<sequence_no>

Hopefully, this helps you out when you get in a jamb.

Cheers!
AJ