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

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

  • Comments 7

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: http://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: http://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: http://help/18805 
The process could not execute 'sp_replcmds' on 'TXDAL01410'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://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_name

from sysarticles art left outer join sys.objects obj

on art.filter = obj.object_id

where 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

Leave a Comment
  • Please add 3 and 2 and type the answer here:
  • Post
Page 1 of 1 (7 items)