How to skip a transaction

Stored procedure sp_setsubscriptionxactseqno allows Distribution Agent to skip transaction(s) in Transactional Replication. Internally this stored procedure sets the last delivered watermark (LSN) stored in subscriber’s MSreplication_subscriptions table. Upon restarting the Distribution Agent return transactions greater that this watermark (LSN) from the Distribution database cache (msrepl_commands).

-- executed inside sp_setsubscriptionxactseqno
UPDATE MSreplication_subscriptions
SET transaction_timestamp = CAST(@xact_seqno AS binary(15)) + CAST(SUBSTRING(transaction_timestamp, 16, 1) AS binary(1))
WHERE UPPER(publisher) = UPPER(@publisher)
AND publisher_db = @publisher_db
AND (publication = @publication
OR (publication = N''
AND independent_agent = 0
AND UPPER(@publication) = N'ALL'))

Skipping Errors

Skipping Errors is useful when Distribution Agent is failing due to invalid transactions. Using sp_helpsubscriptionerrors provides list of invalid transactions.  Supplying xact_seqno to sp_setsubscriptionxactseqno sets the transaction LSN or sequence number as “delivered”, then restarting Distribution Agent will advance the Distribution agent to subsequent transactions, skipping failing transactions.  Once Agent moved past invalid transactions, review root cause to prevent future stoppages.

Example:

sp_helpsubscriptionerrors ‘SQLSver’,‘dbtranpub’,‘dbtranpub_pub’,‘SQLSubscriber’,‘dbtransub’:

id          time                    source_name   error_code   error_text                                     xact_seqno                          command_id

———– ———————– ————- ———— ——————————————– ———————————- ———–

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

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

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

Command to skip the transaction:

sp_setsubscriptionxactseqno ‘SQLSver’,‘dbtranpub’,‘dbtranpub_pub’,0x0000002B000001960003

Skipping Batch

Perhaps a large batch of data changes were run on Publisher and you now observed high latency waiting for that large transaction to be delivered to Subscriber.

How will “skipping” help?

Simply apply the same SQL batch commands directly on the subscriber, then mark the entire Publisher transaction as “delivered”.
Here are various methods to determine the correct xact_seqno to skip:

• Execute sp_browsereplcmds on distribution database, using MSreplication_subscriptions (transaction_timestamp) as @xact_seqno_start parameter.
• Query distribution cache tables msrepl_transactions (entry_time) and msrepl_commands (sum of comman) to identify the large batch transaction.
• Start Distribution Agent with -Output parameter and observe directly the watermark being called in sp_msrepl_get_repl_commands.
• The xact_seqno parameter also appears in Profiler Trace or Extended Events in RPC:Starting events.

Once xact_seqno watermark (LSN) to skip is found, stop the distribution Agent, make matching changes on the subscriber(s), then update “delivered” watermark for each subscriber before restarting the distribution Agent.  If multiple transaction are skipped, set watermark to most recent (highest) value, skipping all transactions in one step.

sp_setsubscriptionxactseqno  ‘publisher’, ‘publisher_db’, ‘publication’,’exact_seqno’

Proactive Maintenance

If “skipping transactions” is part of a proactive maintenance, use Tracer Token to ensure watermark (LSN) includes all prior transactions.

Insert a Tracer Token via Replication Monitor or sp_posttracertoken AFTER performing a batch update.  Then query the distribution database for that Tracer Token’s xact_seqno (LSN) as ” transaction to skip” ensuring Distribution Agent will move past the prior batch of commands since the Trace Token occurs after the batch transactions. Tracer Token show as type = 1073741871 in Msrepl_commands table.

EXEC sys.sp_posttracertoken  @publication = ‘TranProducts’

select t.entry_time, t.xact_seqno 
FROM [dbo].[MSrepl_transactions] t JOIN 
[dbo].[MSrepl_commands] c on t.xact_seqno=c.xact_seqno
where c.type = 1073741871

Chris Skorlinski, SQL Server Escalation Services