Replication failed: process could not execute 'sp_replcmds' error on Publisher with Remote Distributor

Question

Hi all -

We're getting replication errors on our Publisher.
We have a transactional replication running with the following setup:
Publisher, Distributor, and Subscriber are on different servers.
Publisher is SQL Server 2008 Ent; Subscriber is SQL Server 2005 SP2

Can anyone help?

Thanks in advance.


Here's the error from Replication Monitor on the publisher:
===============================================================================================
Error messages:
The process could not execute 'sp_replcmds' on [PublisherDB]. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
The Log-Scan Process failed to construct a replicated command from log sequence number (LSN) {0015e299:0000c980: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 [PublisherDB]. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037


Distributor Error Log:
===============================================================================================
Date        3/14/2011 10:31:54 AM
Log        SQL Server (Current - 3/14/2011 10:31:00 AM)

Source        spid62

Message
Replication-Replication Transaction-Log Reader Subsystem: agent SQL2-LMP-2 failed. The process could not execute 'sp_replcmds' on [PublisherDB].


DBCC OPENTRAN returned:
===============================================================================================
Oldest active transaction:
    SPID (server process ID): 59
    UID (user ID) : -1
    Name          : RECEIVE MSG
    LSN           : (1434268:5251:1)
    Start time    : Mar 14 2011 10:08:01:400AM
    SID           : 0x03935e9322ce1948b02fa82087e0087e

Replicated Transaction Information:
        Oldest distributed LSN     : (1434265:51376:10)
        Oldest non-distributed LSN : (1434265:47430:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Answer 1

What happens when you stop / start the logreader agent for that database?

---

David

Answer 2

To get an idea on how to troubleshoot this issue, You can follow the troubleshooting steps done on this article. This will help you find the reason for the failure. http://blogs.msdn.com/b/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

 

---

Regards, Ashwin Menon My Blog -> sqllearnings.wordpress.com

Answer 3

Did a restart to the logreader help?

 

---

-- Yaniv www.sqlserverutilities.com http://blogs.microsoft.co.il/blogs/yaniv_etrogi

Answer 4

I am getting the same error please help..

---

Thanks

Answer 5

Luv2xlr8, can you try this:

sp_droppublicaton PublicatonName, @ignore_distributor=1

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 6

Luv2xlr8, can you try this:

sp_droppublicaton PublicatonName, @ignore_distributor=1

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Hilary - That doesn't seem to make sense for this post? Wrong thread maybe? 

---

David

Answer 7

Oops - that is correct! Thanks David.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941