Freigeben über

Log Reader Agent Fails with the Error “The Log Reader Agent failed to construct a replicated command from log sequence number (LSN)”

  • Artikel

Recently we encountered a case where in the Log Reader Agent fails to run and the following error is reported in the Replication Monitor

Error messages:
The process could not execute 'sp_replcmds' on 'TXDAL01410'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: https://help/MSSQL_REPL20011

Execution of filter stored procedure 2048634987 failed. See the SQL Server errorlog for more information. (Source: MSSQLServer, Error number: 18764)
Get help: https://help/18764

 The Log Reader Agent failed to construct a replicated command from log sequence number (LSN) {0001d651:00002a49:0006}. Back up the publication database and contact Customer Support Services. (Source: MSSQLServer, Error number: 18805)
Get help: https://help/18805 

 The process could not execute 'sp_replcmds' on 'TXDAL01410'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: https://help/MSSQL_REPL22037

CAUSE
=====
The above Error indicates the Log Reader Agent is stuck at LSN {0001d651:00002a49:0006} and is not able to read and create a command for this LSN.
As the Log Reader Agent is stuck at the LSN, it is not moving ahead and since we can have only 1 Log Reader Agent in Transactional Replication which is used for all the publications. All the publications is showing error in the replication monitor and is not moving ahead.

 We checked the transaction at the LSN {0001d651:00002a49:0006}. 

From the above Error we see “Execution of filter stored procedure 2048634987 failed”  

 We checked the Filter Stored Procedure ID from sysarticles and found that it was the filter stored procedure for table dbo.booking 
Select * from sysarticles where filter=2048634987

 However the entry of the filter stored procedure was missing in the sysobjects tables which indicate that the filter stored procedure was deleted due to some reason. To confirm this we used the following query 

select * from (select art.name as article_name, art.pubid as publication_id, art.artid as article_id, art.filter as filter_proc_id, isnull(obj.name,'## MISSING!! ##') as filter_proc_namefrom sysarticles art left outer join sys.objects obj on art.filter = obj.object_idwhere art.filter > 0)as results -- and obj.name is null (use when only the mismatched articles where required).where filter_proc_name='## MISSING!! ##'

 

 

 Output: 

scott                     750          4343        77151966                 ## MISSING!! ##
test                       750          4336        2048634987             ## MISSING!! ##
tiger                      750          4337        2096635158             ## MISSING!! ##


 As seen from the above output, we have 3 tables viz scott,test,tiger all belonging to the same publication with the pubid = 750 have their filter stored procedure missing in the sysobjects.

RESOLUTION
=========
So to resolve this we identified the publications from syspublications table with pubid=750.
We then deleted the filtering for the above 3 articles and re-added them and the stored procedure got recreated.
We then recreated the snapshot for that publication and marked it’s subscriber for re-initialization.
The Snapshot agent recreated the snapshot and Log Reader Agent started running fine without error.
We checked the replication Monitor and the error vanished..

Parikshit Savjani,
SE, Microsoft SQL Server

Reviewed By Anurag Sharma, SQL Server Escalation Services

Comments

  • Anonymous
    December 30, 2008
    PingBack from http://www.codedstyle.com/log-reader-agent-fails-with-the-error-%e2%80%9cthe-log-reader-agent-failed-to-construct-a-replicated-command-from-log-sequence-number-lsn%e2%80%9d/

  • Anonymous
    July 24, 2009
    Hi, Wrote wrote: "We checked the transaction at the LSN {0001d651:00002a49:0006}.".  How did you do this? We have a similar problem and we don't know how to get the content of the LSN. Nige.

  • Anonymous
    July 24, 2009
    The comment has been removed

  • Anonymous
    August 07, 2009
    This is great! I had been struggling with this for a number of days until I found this article.  The only part that is troubling me is why do the filter procs disappear?

  • Anonymous
    December 21, 2009
    Hello, I have exactly the same sympthoms, but the filtering article is present in the publication database. Appreciate your help. Thanks, Alexander

  • Anonymous
    August 07, 2014
    Hi, But if we re-initialize the subscription. It will take a new snapshot will all publish all the tables.

  • Anonymous
    October 02, 2014
    Still its same error , again process all steps

  • Anonymous
    December 19, 2016
    Really Helpfull Post. Saved my lot of time :) Thanks!

  • Anonymous
    December 30, 2016
    The comment has been removed

  • Anonymous
    January 09, 2017
    The comment has been removed