Solved

The process could not execute 'sp_replcmds'

Posted on 2009-05-06
21
1,863 Views
Last Modified: 2012-05-06
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.


SQLDump0537.txt
0
Comment
Question by:Heartnet
  • 14
  • 7
21 Comments
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 24317239
0
 

Author Comment

by:Heartnet
ID: 24317345
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.
0
 

Author Comment

by:Heartnet
ID: 24317930
Also I just noticed the KB applies to SQL 2000 and it doesn't mention about SQL 2005.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24317985
And what about updating to SP 3?
0
 

Author Comment

by:Heartnet
ID: 24318165
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
0
 

Author Comment

by:Heartnet
ID: 24321657
I have updated to SP3 but the issue still remains.
Any idea?
0
 

Author Comment

by:Heartnet
ID: 24321694

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
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24325530
So, Did you check the error log for details? Can you post the log details here?
0
 

Author Comment

by:Heartnet
ID: 24326803
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

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

sqLErrorLog.log
sqllog.log
0
 

Author Comment

by:Heartnet
ID: 24327081
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.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Heartnet
ID: 24328353
This is exactly what I have:
http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/3be6e7de-121b-43d3-87a9-d0be111cc861/

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.

http://support.microsoft.com/default.aspx/kb/936305

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

0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 500 total points
ID: 24328587
Hi,
 
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]
REBUILD WITH (ONLINE = ON);
Please check this link for more info:
http://technet.microsoft.com/en-us/library/bb794627.aspx
 
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24328660
I know you've mentioned that you have all latest patches. But just in case, check this link:
http://support.microsoft.com/kb/923296 
0
 

Author Comment

by:Heartnet
ID: 24329153
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 :(
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24331718
Ok, by reviewing your log, I've noticed this:
1)  
Error,[382] Logon to server '(local)' failed (DisableAgentXPs)
http://social.msdn.microsoft.com/forums/en-US/sqlgetstarted/thread/73327808-c24d-4b01-be70-840c15c9ff3a/
 
2)
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:

http://www.developmentnow.com/g/118_2003_12_0_0_466527/SQL-Agent-wont-start.htm
 
0
 

Author Comment

by:Heartnet
ID: 24331762
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.
0
 

Author Comment

by:Heartnet
ID: 24331905
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.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24331950
0
 

Author Comment

by:Heartnet
ID: 24337567
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:

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

Problem:
metadata for replication got corrupted.

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

0
 

Author Closing Comment

by:Heartnet
ID: 31578554
I want to award points to you for helping and accepted the solution that I did because that's the one that fixes it.
0
 

Author Comment

by:Heartnet
ID: 24337650
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.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

4 Experts available now in Live!

Get 1:1 Help Now