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"
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