SQLServerWiki

“The Only Thing That Is Constant Is Change”

How To Make Log Reader Agent skip a Transaction.

Posted by database-wiki on April 11, 2016

We were getting an error in the LogReader and so we decided to just skip that
transaction.

Status: 0, code: 18805, text: ‘The Log Reader Agent failed
to construct a replicated command from log sequence number (LSN)
{000196f9:000332dc:006b}. Back up the publication database and contact Customer
Support Services.’.

Below are the repro steps using Northwind Shippers table we walked through to
instruct the Log Reader to skip a transaction. This repro was tested in SQL 2005
SP2. It was not tested but should also work for SQL 2000.

NOTE: This transaction may contain data such as INSERT which if not replicated to
Subscriber may cause failure when a subsequent UPDATE is applied. Customer should
plan some method to verify missing transaction once all other transactions have been replicated.

Resolution:

–Steps to have LogReader skip a transaction

–STOP and DISABLE the LogReader Job

–Update 1 row
update Shippers
Set CompanyName = ‘Pub2’
where ShipperID = 2
Go

–Get last distributed transaction
dbcc opentran
Oldest distributed LSN : (240:40:4)
000000F0:00000028:0004

–show all transactions newer then last distributed
select * from fn_dblog(‘0x000000F0:00000028:0001’, null)

Current LSN Operation Context
Transaction ID
———————– ——————————-
——————————- ————–
000000f0:00000028:0001 LOP_BEGIN_XACT LCX_NULL
0000:00001f69
000000f0:00000028:0002 LOP_DELETE_ROWS LCX_CLUSTERED
0000:00001f69
000000f0:00000028:0003 LOP_INSERT_ROWS LCX_CLUSTERED
0000:00001f69
000000f0:00000028:0004 LOP_COMMIT_XACT LCX_NULL
0000:00001f69

–new transaction Set CompanyName = ‘Pub2’
000000f0:0000002a:0001 LOP_BEGIN_XACT LCX_NULL
0000:00001f6a
000000f0:0000002a:0002 LOP_DELETE_ROWS LCX_CLUSTERED
0000:00001f6a
000000f0:0000002a:0003 LOP_INSERT_ROWS LCX_CLUSTERED
0000:00001f6a
000000f0:0000002a:0004 LOP_COMMIT_XACT LCX_NULL
0000:00001f6a

(8 row(s) affected)

–Update 1 row
update Shippers
Set CompanyName = ‘Pub3’
where ShipperID = 3
Go

–show all transactions newer then last distributed
select * from fn_dblog(‘0x000000F0:00000028:0001’, null)

Current LSN Operation Context
Transaction ID
———————– ——————————-
——————————- ————–
000000f0:00000028:0001 LOP_BEGIN_XACT LCX_NULL
0000:00001f69
000000f0:00000028:0002 LOP_DELETE_ROWS LCX_CLUSTERED
0000:00001f69
000000f0:00000028:0003 LOP_INSERT_ROWS LCX_CLUSTERED
0000:00001f69
000000f0:00000028:0004 LOP_COMMIT_XACT LCX_NULL
0000:00001f69
–new transaction Set CompanyName = ‘Pub2’
000000f0:0000002a:0001 LOP_BEGIN_XACT LCX_NULL
0000:00001f6a
000000f0:0000002a:0002 LOP_DELETE_ROWS LCX_CLUSTERED
0000:00001f6a
000000f0:0000002a:0003 LOP_INSERT_ROWS LCX_CLUSTERED
0000:00001f6a
000000f0:0000002a:0004 LOP_COMMIT_XACT LCX_NULL
0000:00001f6a
–new transaction Set CompanyName = ‘Pub3’
000000f0:0000002c:0001 LOP_BEGIN_XACT LCX_NULL
0000:00001f6b
000000f0:0000002c:0002 LOP_DELETE_ROWS LCX_CLUSTERED
0000:00001f6b
000000f0:0000002c:0003 LOP_INSERT_ROWS LCX_CLUSTERED
0000:00001f6b
000000f0:0000002c:0004 LOP_COMMIT_XACT LCX_NULL
0000:00001f6b

(12 row(s) affected)

–Skip the Pub2 transactions by marking it as replicated
sp_repldone @xactid = 0x000000f00000002a0001, @xact_segno = 0x000000f00000002a0004,
@numtrans = 1, @time = 0
go
sp_replflush

–Restart LogReader
–LogReader executes sp_repldone to verify last transaction exists in Distribution
DB.
–It should error as the LogReader LSN doens’t match Distribution DB LSN

2008-03-10 17:13:25.496 Publisher: {call sp_repldone ( 0x000000f0000000280004,
0x000000f0000000280004, 0, 0)}
2008-03-10 17:13:25.496 Status: 0, code: 20011, text: ‘The process could not
execute ‘sp_repldone/sp_replcounters’ on ‘SKOR380′.’.
2008-03-10 17:13:25.496 The process could not execute ‘sp_repldone/sp_replcounters’
on ‘SKOR380’.
2008-03-10 17:13:25.496 Status: 0, code: 18768, text: ‘The specified LSN
{000000f0:00000028:0004}
for repldone log scan occurs before the current start of replication in the log
{000000f0:0000002a:0004}.’.

–LSN {000000f0:00000028:0004}
–sp_repldone ( 0x000000f0000000280004, 0x000000f0000000280004, 0, 0)

–Show last transaction in the Distribution DB for this publisher_database_id

–First retrieve the publisher_database_id
select * from MSsubscriptions

publisher_database_id
———————
1

–Next find “xact_id” for the xact_seqno showing in LogReader Error
–sp_repldone ( 0x000000f0000000280004, 0x000000f0000000280004, 0, 0)

select * from distribution.dbo.msrepl_transactions
where publisher_database_id = 1
where xact_seqno = 0x000000f0000000280004

publisher_database_id xact_id xact_seqno
entry_time
——————— ———————————-
———————————- ———————–
1 0x000000F0000000280001 0x000000F0000000280004
2008-03-10 12:09:20.050

–Verify Transaction
select top 1 * from distribution.dbo.msrepl_transactions
where publisher_database_id = 1
order by xact_id desc

publisher_database_id xact_id xact_seqno
entry_time
——————— ———————————-
———————————- ———————–
1 0x000000F0000000280001 0x000000F0000000280004
2008-03-10 12:09:20.050

/*
Transaction marked as Replicated
sp_repldone
@xactid = 0x000000f00000002a0001,
@xact_segno = 0x000000f00000002a0004

Existing Transaction
0x000000f0000000280004
LSN {000000f0:00000028:0004}

Update Distribution DB to reflect sp_repldone transaction
*/

–Update existing begin tran and commit tran with sp_repldone skipped transaction
update distribution..msrepl_transactions set
xact_id=0x000000f00000002a0001,
xact_seqno=0x000000f00000002a0004
where xact_id=0x000000F0000000280001

–Update existing commit tran with the sp_repldone skipped commit tran
update distribution..msrepl_commands set
xact_seqno=0x00000018000001e80004
where xact_seqno =0x000000f0000000280004

–Distribution DB should now contain sp_repldone skipped transaction
select top 1 * from distribution.dbo.msrepl_transactions
where publisher_database_id = 1
Order by xact_id desc

publisher_database_id xact_id xact_seqno
entry_time
——————— ———————————-
———————————- ———————–
1 0x000000F00000002A0001 0x000000F00000002A0004
2008-03-10 12:09:20.050

select top 1 * from distribution.dbo.msrepl_commands
where publisher_database_id = 1
order by xact_seqno desc

publisher_database_id xact_seqno type article_id
——————— ———————————- ———– ———–
1 0x000000F000000024000A 1073741835 3

–Start LogReader Agent
— 1) LogReader verifies last transaction in Distribution db matches skipped
transaction
2008-03-10 17:35:18.708 Publisher: {call sp_repldone ( 0x000000f00000002a0004,
0x000000f00000002a0004, 0, 0)}
2008-03-10 17:35:18.708 Publisher: {call sp_replcmds (500, 0, 0, , 0, 500000)}

— 2) LogReader picks up the next pending transaction
2008-03-10 17:35:18.723 Publisher: {call sp_repldone ( NULL,
0x000000f00000002c0004, 0, 16)}
2008-03-10 17:35:18.723 Publisher: exec sp_replcounters N’TranPub’
2008-03-10 17:35:18.723 OLE DB Publisher ‘SKOR380′: exec sp_replcounters
N’TranPub’
2008-03-10 17:35:18.723 Status: 4, code: 22021, text: ‘Batch committed.
Batch consisted of 1 commands, 1 xacts.
Last xact: 0x000000f00000002c0001, ‘0x000000f00000002c0004′.’.

–Start the Distribution Agent and confirm skipped row doesn’t appear
— but the next pending does appear
select * from TranSub.dbo.Shippers

ShipperID CompanyName Phone
———– —————————————- ————————
3 Pub3 (503) 555-9931

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: