Using sp_repldone to SKIP a transaction

Chris Skorlinski
Microsoft SQL Server Escalation Services

The steps in this posting covered advanced topics is SQL Server Transactional Replication.  If you are unsure on how or when to use these steps, DON’T, instead contact Microsoft SQL Server Support or post your problem on the TechNet forum/newsgroup.  These steps will result in data loss, use at your own discretion.

The action taken in steps below will SKIP a pending transaction.  If that transaction is a DELETE, then the DELETE will not be pushed to the Subscriber.  If you then UPDATE the Publisher, when that UPDATE is pushed to the Subscriber it will fail with a “row not found”. At this point you can either SKIPERRORS, or reinitialize the Subscriber by pushing down a new snapshot.

--Steps to have Transactional Replication LogReader

--    "skip" the next pending transaction

 

--WARNING: Use at your own risk as these steps can lead to

--    Subscribers out-of-sync with Publisher resulting in

--  Distribution Agent failing with "row not found",

--  necessitating a Publication reinitialize.

 

--Find "next" pending transaction

Use <published database>

GO

dbcc opentran

Go

 

      Replicated Transaction Information:

        Oldest distributed LSN     : (86:6614:4)

        Oldest non-distributed LSN : (86:6617:1) <-- 0x00000056000019D90001

 

--Retrieve from the Transaction log the BeginTran and CommitTran LSN values

--For large transactions, TOP 10 value may need to be increased.      

select TOP 10 [Current LSN], [Operation], [Transaction ID] from fn_dblog('0x00000056:000019D9:0001', null)

 

--Look for LOP_COMMIT_XACT for the "Transaction ID" matching the LOP_BEGIN_XACT

Current LSN             Operation                       Transaction ID

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

00000056:000019d9:0001  LOP_BEGIN_XACT                  0000:000200fe<--Begin Transaction

00000056:000019d9:0002  LOP_DELETE_ROWS                 0000:000200fe

00000056:000019d9:0003  LOP_INSERT_ROWS                 0000:000200fe

00000056:000019d9:0004  LOP_COMMIT_XACT                 0000:000200fe<--Matching Commit Tran

00000056:000019dc:0001  LOP_BEGIN_XACT                  0000:000200ff

00000056:000019dc:0002  LOP_MARK_SAVEPOINT              0000:000200ff

00000056:000019dc:0003  LOP_REPL_NOOP                   0000:000200ff

00000056:000019dc:0004  LOP_PREP_XACT                   0000:000200ff

 

--Tell Replication that BeginTran/CommitTran pair are already REPLICATED

--Note the format change to the LSN values, ":" are not used in sp_repldone

sp_repldone @xactid = 0x00000056000019D90001, @xact_segno = 0x00000056000019D90004

 

--Verify that the “skipped” transaction is now "Oldest distributed LSN"

dbcc opentran

      Replicated Transaction Information:

                  Oldest distributed LSN     : (86:6617:4)  <-- 00000056:000019d9:0004

                  Oldest non-distributed LSN : (86:6689:1)

       

--Release article cache      

sp_replflush

 

--Reset LogReader to retrieve next transactions

sp_replrestart

 

--Start LogReader and begin Replicating transactions