Replication – Execution of filter stored procedure 678449641 failed.

ERROR:

Error messages:
The process could not execute ‘sp_replcmds’ on ‘TCY-SQL01\SQLDEV’. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
Execution of filter stored procedure 678449641 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) {000ff0ac:00000520:003e}. 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 ‘TCY-SQL01\SQLDEV’. (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!! ##’

Resolution

I deleted the publication, recreated it and then created a subscription without initialisation. this prevented a full snapshot.

OR

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

 

Useful Links

http://www.sqlwebpedia.com/content/replication-failed-with-log-reader-agent-failed-construct-command

http://dbaspot.com/forums/ms-sqlserver/365994-log-reader-agent-error-execution-filter-stored-procedure-faile.html

http://www.sqlservercentral.com/Forums/Topic105590-7-1.aspx

http://blogs.msdn.com/sqlserverfaq/archive/2008/12/30/log-reader-agent-fails-with-the-error-the-log-reader-agent-failed-to-construct-a-replicated-command-from-log-sequence-number-lsn.aspx