SQLServerWiki

“The Only Thing That Is Constant Is Change”

How to reset Log Reader Agent if LSN no longer matches.

Posted by database-wiki on April 11, 2016

Problem Description:

Publisher: {call sp_repldone ( 0x00000000000000000000, 0x00000000000000000000, 0,
0)}
Status: 0, code: 0, text: ‘The process could not execute
‘sp_repldone/sp_replcounters’ on ‘BORGSQL1′.’.
The process could not execute ‘sp_repldone/sp_replcounters’ on ‘BORGSQL1’.
Repl Agent Status: 6
Status: 0, code: 18768, text: ‘The specified LSN (00000000:00000000:0000) for
repldone log scan occurs before the current start of replication in the log
(000828b2:00003657:0003).’.
Status: 0, code: 22017, text: ‘The process could not set the last distributed
transaction.’.
Disconnecting from Publisher ‘BORGSQL1’

Resolution:

Executed “dbcc opentran” on the published database. There were open
transactions.

Transaction information for database ‘Gemini’.
Replicated Transaction Information:
Oldest distributed LSN : (534706:13911:3)
Oldest non-distributed LSN : (534706:13913:1)
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.

—————————————
We did not want to execute sp_repldone null,nul,0,0,1 since this would mark the
pending transactions as replicated. They would not get moved to the subscriber.

Instead we updated the Distribution database with the proper values from the
transaction log.

For EXAMPLE:

To get the log reader running again, you can update msrepl_transactions with the
values obtained from sp_repltrans by using the following procedure:

USE published_db
GO
sp_repltrans

/*The output gives the xact_id and xact_seqno for the next replication transaction.
If no rows are returned, create some dummy INSERT or UPDATE into any published
articles, and then run sp_repltrans again. If multiple rows are returned, get the
xact_id and xact_seqno from the first row. */

USE distribution
GO
SELECT id FROM MSpublisher_databases WHERE publisher_db = ‘publisher_db’

/*Use the output from sp_repltrans and the id for publisher_db in
MSpublisher_databases table to update the msrepl_transactions table.
For example, the output from sp_repltrans is:

xactid xact_seqno
———————- ———————-
0x000000110003028E0001 0x000000110003028E0004

The id from MSpublisher_databases in this example is 2. Then run the following
queries to update the MSrepl_transactions table:
*/

EXEC sp_configure ‘allow updates’,1
GO
RECONFIGURE WITH OVERRIDE
GO

/*
Before this step, make sure all the distribution agents to the publisher_database
run successfully and have applied all jobs to subscribers.
You use select into to create a copy of the distribution..Msrepl_transactions table
in the Pubs database. You must enable Pubs database to allow select into db option.

*/

USE distribution
GO
SELECT * INTO tempdb..Msrepl_transactionsBackup
FROM distribution..Msrepl_transactions
GO
DELETE FROM MSrepl_transactions WHERE publisher_database_id = 2
GO
INSERT INTO MSrepl_transactions
VALUES (2,0x000000110003028E0001, 0x000000110003028E0004, 0)
GO
EXEC sp_configure ‘allow updates’,0
GO
RECONFIGURE WITH OVERRIDE
GO

To verify open transaction output execute:

dbcc opentran

Output:
——————–
Replicated Transaction Information:
Oldest distributed LSN : (25:476:7)
Oldest non-distributed LSN : (26:94:1)

Then execute :

sp_repltrans

Output:
———————

xdesid xact_seqno
———————- ———————-
0x0000001A0000005E0001 0x0000001A0000005E0007

The xdesid matches the oldest non-distributed transaction. In other words, the
next transaction to be picked up by the log reader.

0x0000001A 0000005E 0001
26 94 1

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: