Solved

The process could not execute 'sp_replcmds'

Posted on 2009-05-06
21
1,859 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
Comment Utility
0
 

Author Comment

by:Heartnet
Comment Utility
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
Comment Utility
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
Comment Utility
And what about updating to SP 3?
0
 

Author Comment

by:Heartnet
Comment Utility
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
Comment Utility
I have updated to SP3 but the issue still remains.
Any idea?
0
 

Author Comment

by:Heartnet
Comment Utility

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
Comment Utility
So, Did you check the error log for details? Can you post the log details here?
0
 

Author Comment

by:Heartnet
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Heartnet
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
0
 

Author Comment

by:Heartnet
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

771 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

13 Experts available now in Live!

Get 1:1 Help Now