The process could not execute 'sp_replcmds' even after reinitialization

Question

The process could not execute 'sp_replcmds' even after reinitialization

Sherri Barkley

SSCrazy

Points: 2269
More actions
February 18, 2010 at 5:25 pm

#220720

I am a rookie DBA, but unfortunately I am also the only DBA the company has. We have transactional replication set up with a publisher, separate distributor and 2 subscribing servers. My replication has failed with the following error:
Error messages:
The process could not execute 'sp_replcmds' on '<servername>'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
Execution of filter stored procedure 1014603790 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) {00000c20:0001bd67:000f}. 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 '<servername>'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037
I tried reinitializing the publications and generated new snapshots, but when the log reader agent runs it still gets this same error message. Is there anything I can do? I'm thinking if not then the next step is to delete the publications and recreate them. I really don't want to do that since I have several publications, but I really need to get this working.
Does anyone have any other suggestions?
Thanks
Sherri

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

There have been cases this has happened when you have filters set up in your replication. This is more likely a bug. I think you should contact Customer Support Services at Microsoft itself.

-Roy

Sherri Barkley SSCrazy Points: 2269 More actions · Answer 2

Thanks Roy. However, the replication has been set up and running (not modified) for a couple of months now and just started failing. So if it was related to the filtering wouldn't it have failed before? Also, I have 11 publications and only half of those have any filtering at all (only 1 has row filtering the others are at the article level), but all of them are failing. Could one or two publications failing cause all of the rest to fail as well?

Sherri Barkley SSCrazy Points: 2269 More actions · Answer 3

After speaking with Microsoft it turns out there was some bad data in my table that was causing the filter to fail. We changed the filter to take that bad data into account since my server is not the source of the data we had to plan for the worst in the future.

By taking the error message (Execution of filter stored procedure 1014603790 failed) and querying the sysarticles table we were able to narrow down which filter was causing the problem.

Select * from sysarticles where filter=1014603790

Then we set up a profiler trace on the publisher to get the exact problem data which we could then query the database for. SSMS has different "rules" than sp_replcmds because we could run the filter against that table in SSMS and it didn't fail, but sp_replcmds it would.

I wanted to update this with our resolution in case it help someone else in the future.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 4

Thanks for the update. I am sure someone else could gain from the knowledge you had just given.

-Roy