The process could not execute 'sp_replcmds'

I recently took down existing replication for the tables update and when I rebuild the replication on MS SQL 2005 SP2 with latest patch update dated Feb 11, 2009, the log agent shows: The process could not execute 'sp_replcmds'

I have tried to reinitialize the subscriptions, rebuild the replications 3 times, run DBCC CHECKDB and found no errors.

Attached is the SQL Dump error that I keep getting when replication is trying to go through.

Who is Participating?
HeartnetAuthor Commented:
I tried that KB. I tried to set querytimeout = 0 but got this error:
'0' is not a valid value for the '-QueryTimeout' parameter. The value must be an integer greater than or equal to 1.

I tried to set querytimeout to 9000 from 1800 default value and readbatchsize to 250 from 500 default value, it's not working. I tried with value 5400 and 300 for each respective parameter and doesn't work either.

I will update it but at the moment i can't do it due to usage.
HeartnetAuthor Commented:
Also I just noticed the KB applies to SQL 2000 and it doesn't mention about SQL 2005.
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

And what about updating to SP 3?
HeartnetAuthor Commented:
I can only perform update after business hour. Right now the database is being used. I will run update. Aside from SP3, there's nothing else I can do in the mean time to fix this? In case updating to SP3 doesn't fix the issue
HeartnetAuthor Commented:
I have updated to SP3 but the issue still remains.
Any idea?
HeartnetAuthor Commented:

Here's the same error messages from Replication Monitor:

Error messages:
The process could not execute 'sp_replcmds' on 'VNSV20001'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011 
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support. (Source: MSSQLServer, Error number: 3624)
Get help: http://help/3624 
The process could not execute 'sp_replcmds' on 'VNSV20001'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037
So, Did you check the error log for details? Can you post the log details here?
HeartnetAuthor Commented:
Attached are SQL Log SQL Server Logs and SQL Error Log from Error Logs.

The following may be what you are looking for:
Date            5/6/2009 7:08:58 PM
Log            SQL Server (Archive #1 - 5/6/2009 7:09:00 PM)

Source            spid69

SQL Server Assertion: File: , line=2258 Failed Assertion = 'colidx < pSchema->schema_re.numcols'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.

HeartnetAuthor Commented:
We have other replication running from different database than this one and they are running fine. I suspect something in that particular database causing it but I can't quite find the problem.
HeartnetAuthor Commented:
This is exactly what I have:

As an reply to the question posted in the link above,
1. Do you have any custom Online Rebuild Index Jobs / Index Rebuild Jobs on these servers ?
>>>How do I check for this?
2. Do you see any mdmp (Stack Dumps) ?
>>>My stack dump is attached on the first question

3. If yes please send mdmp and logs to PSS support ,  you might be running into a issue which got fixed in 3175.

>>>Updating to SP3 should cover this one, I think.
Another workaround is to stop log reader agent during index rebuild operation , which may not be a valid option.
>>> Not too sure how to check for index rebuild operation either.

I'm desperately trying to find the problem and fix this asap. Any idea/help would be greatly appreciated.

ralmadaConnect With a Mentor Commented:
Online rebuild option is enabled when you create your index like this:
ALTER INDEX [ClusteredIndex_default_partition]
ON dbo.[MG_Strategic_Plan_MeasureGroup_default_partition]
Please check this link for more info:
I know you've mentioned that you have all latest patches. But just in case, check this link: 
HeartnetAuthor Commented:
I check the link and see that the hotfix only applies if the sql dump has the following:
   2006-05-22 14:22:07.49 spid101     * Location:       logscan.cpp:2277
   2006-05-22 14:22:07.49 spid101     * Expression:       startLSN >= m_curLSN

It's not the same as what I have here. I have also tried to truncate the transaction log and see if there's anything in there. Doesn't seem to work either :(
Ok, by reviewing your log, I've noticed this:
Error,[382] Logon to server '(local)' failed (DisableAgentXPs)
Error,[165] ODBC Error: 0<c/> Driver's SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]

Could it be that there's an issue with your MDAC dll and SQL? Please check this link:
HeartnetAuthor Commented:
I'm checking both link at the moment. But I'm able to start and restart SQL Server Agent without much problem. I tested one of the job and it runs fine.
HeartnetAuthor Commented:
How do I check whether there's issue with mdac dll? To me, SQL Agent seems to be fine. But I want to make sure of it by further checking mdac.
HeartnetAuthor Commented:
Hi Ral,

Thank you for the assistance. I checked it out and it doesn't seem to fix the problem.

However, you did give me idea on checking index on table and after I talked with our developers and obtained the changes on each tables that changes before and after the update to the database, one of the tables has error that caused the metadata for replication corrupted. I rebuild the whole replication and enable/disable publisher and distributor replication and it fixed!

Here's what I did, just for FYI for anyone else that may encounter this issue:

1.      sp_replcmds cant be executed during Log Agent run.
2.      No matter what you do even with double coffee cup, it doesnt fix a thing.

metadata for replication got corrupted.

1.      Delete all subscriptions
2.      Delete all publications
3.      Disable publisher replication
4.      Disable distributor replication
5.      Enable Distributor replication
6.      Enable Publisher replication
7.      Create each publication and let each snapshot finish.
8.      Create subscriptions
9.      Verify Snapshot Agent status on each publication
10.      Verify Log Agent status on each publication
11.      Verify Synchronization status on each subscription
12.      Verify data on replicated table publisher and subscriber matched.
13.      Once everything is verified, its confirmed its done.

HeartnetAuthor Commented:
I want to award points to you for helping and accepted the solution that I did because that's the one that fixes it.
HeartnetAuthor Commented:
Hmm... I tried to make my last comment as solution because that's the ACTUAL solution for fixing this. But I also want to award points for people who tried to help me. Not sure how to do that and it appears I did it wrong as I don't see the solution being accepted on my comment instead.

If moderator can fix this that would be helpful.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.