How to skip a transaction in SQL 2005/2008 Transactional Replication


Once in a while you may find the need to skip an indiviual transaction in SQL 2005/2008 Transactional Replication. For example, when Distribution Agent fails because of an error and the database integrity won't be impacted by skipping that transactions.  For instructing the Distribution Agent to skip a transaction, SQL provides 2 very helpful stored procedures.  sp_helpsubscriptionerrors can be used to display the list of invalid transactions.  The key output from this SP is the xact_seqno which is supplied to the second stored procedure sp_setsubscriptionxactseqno as the transaction LSN or sequence number to be skipped. 


sp_helpsubscriptionerrors


http://msdn.microsoft.com/en-us/library/ms173427.aspx


 


sp_setsubscriptionxactseqno


http://msdn.microsoft.com/en-us/library/ms188764.aspx


Note the BOL topic incorrectly says "Is the LSN of the next transaction at the Distributor to be applied at the Subscriber" when it is actual the LSN of the transaction the Distributor will SKIPP.  We're working to get BOL updated.


EXAMPLE


sp_helpsubscriptionerrors 'SQLw2k8','dbtranpub','dbtranpub_pub','SQLw2k8Subscriber','dbtransub':


 


id          time                    error_type_id source_type_id source_name   error_code   error_text                                   xact_seqno                         command_id  session_id


----------- ----------------------- ------------- -------------- ------------- ------------ -------------------------------------------- ---------------------------------- ----------- -----------


16          2009-09-08 05:14:04.673 0             0              MSSQL_ENG     8152         String or binary data would be truncated.    0x0000002B00000196000300000000     1           0


16          2009-09-08 05:14:04.660 0             0              MSSQL_ENG     8152         String or binary data would be truncated.    0x0000002B00000196000300000000     1           0


16          2009-09-08 05:14:04.660 0             1              MSSQL_ENG                  if @@trancount > 0 rollback tran             0x0000002B00000196000300000000     1           0


 


Command to skip the transaction:


sp_setsubscriptionxactseqno 'SQLw2k8','dbtranpub','dbtranpub_pub',0x0000002B000001960003


 


Many thanks to Amit Banerjee, Microsoft SQL Technical Lead, for the example and testing of these SPs.


Chris Skorlinski, Microsoft SQL Server Escalation Services


 

Comments (1)

  1. RaviKiran says:

    Hi

    i Followed the steps which you suggested to skip a transactions.

    It was working successfully,

    when i check the undistributed Transactions in Replication Monitor, There are showing 1 pending transactions.

    After skipping a transactions I test few records inserted into the database, Replication is also working fine.

    Could you please let me know the why its showing 1 undistributed Transactions.

    Please help me.

Skip to main content