April 15, 2014 at 2:10 am
Hi
We had a problem with transactional replication yesterday whereby we started to receive the error:
Replication Transaction Log Reader SubSystem agent SERVERNAME-32 failed. The process could not execute 'sp_replcmds' on ServerName
Error 14151 Severity 18 state 1
Having looked at the actual log reader agent and selecting 'details' in the Agent History I see more detailed info:
The process could not execute 'sp_repl' commands.
Execution of filtered stored Procedure 123456789 failed. See the server errorlog for more info
The log scan Process failed to construct a replicated command from log sequence number (LSN){000155e4:0007892c:0004}
Back up the publication database........
I have managed to get replication running again by running:
Exec sp_repldone @xactid = Null, @xact_seqno = null,@numtrans = 0, @time = 0,@reset =1
then
EXEC sp_replflush
This has appeared to fix it
However when i run
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!! ##'
It returns MISSING info (4 rows to be precise) as below
article_namepublication_idarticle_idfilter_proc_idfilter_proc_name
TaskP 30 1337 107720032 ## MISSING!! ##
ServiB 30 1327 775201970 ## MISSING!! ##
ServiA 30 1328 1823202141 ## MISSING!! ##
Servic 30 1329 187120231 ## MISSING!! ##
I am monitoring Replication Monitor and all seems ok so far.
Can anybody advise on how I can find out what has caused this to fail (as mentioned first error recorded in log yesterday afternoon)
Secondly should I be concerned about the 'missing info' returned from the above query ?
July 11, 2018 at 9:29 am
Hello,
I realise this post is 4 years old however - did you ever find out what was causing the filter procs to disappear?
Our shop is SQL Server 2008 R2 SP3 using transactional replication and struggling to pinpoint the cause. DDL (auditing) triggers don't seem to pickup the dropped procs.
Cheers,
SQL_Si
July 16, 2018 at 11:40 am
simon.jones.p - Wednesday, July 11, 2018 9:29 AMHello,I realise this post is 4 years old however - did you ever find out what was causing the filter procs to disappear?
Our shop is SQL Server 2008 R2 SP3 using transactional replication and struggling to pinpoint the cause. DDL (auditing) triggers don't seem to pickup the dropped procs.
Cheers,
SQL_Si
If you are having issues with errors like: Execution of filter stored procedure nnnnnn failed, refer to the following article which walks you through some queries to help identify and correct the issue:
Log Reader Agent Fails with the Error “The Log Reader Agent failed to construct a replicated command from log sequence number (LSN)â€
Sue
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply