Process could not execute 'sp_replcmds'

Question

Process could not execute 'sp_replcmds'

pdchandran

Ten Centuries

Points: 1072
More actions
December 8, 2009 at 10:21 am

#99972

Hi all,
We have Sql Server 2000 Ent Edition with SP4. There is a heavily used OLTP database from which we publish articles to various subscribers to offload reporting activity. We observe that whenever we run the Index ReOrganize job (desingned using the Maintenance Plan wizard) the replication fails. Failure point it the logreader for all the publications in the database. The err msg is - Process could not execute 'sp_replcmds'. We had to fire the sp_Repldone command to get this working again. Can someone pls advise as to why the log-reader fails when we run the Index maintenance. We have now faced this issue a couple of times and had to advertise downtime for just carrying out the index maintenance job.

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

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

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

We have never had this problem... But the only thing I can think off is your Disk IO maybe quite high. Replication uses the Log file and if your data file is also on the same physical drive, then there could be IO bottle neck. Thats the only thing I can think off.

Or it could be that you are getting blocking of some kind. Did you look for blocking?

-Roy

pdchandran Ten Centuries Points: 1072 More actions · Answer 2

Hi Roy,

Thanks for your comments.

Yes we do have the data files and the log files residing on the same drive. I had already recommended adding an additional drive to isolate the data and log files from a performance improvement view point. Probably once this done we will run the Index ReOrganize job and will let you know the results. Then we will know if it is due to a Disk IO problem. We didnt check for blocking (database level) at that point.

José.Cruz Right there with Babe Points: 722 More actions · Answer 3

Hi there,

Just because once i had a similar problem and never figured out why it hapened. How often do you run the maintenace job? As the system as high activity is it possible that when you run the maintenace job, there's a lot of "work" to be done? Could you try to run the maintenace job more often?

Also i've seen this message (warning) during a heavy process execution that produced a huge quantity o transaction log information, but when the process finishs log reader was able to catch up, in your case it seems that the replication process stops.

José Cruz

Vinay Thankur SSCarpal Tunnel Points: 4178 More actions · Answer 4

I could do the following:

1. Create a custom profile query timeout and other parameter can be increased and see will that help?

2. create a custom job which runs every hr and check is log reader is running and if its failed run it.

3. try to run the reindex job conditionally or divide it.

4. Check what else activity is running during that time. - in our environment system backup was conflicting with reindex job.

5. divide reindex and other maintainance job so as to distribute the load on the system at the same time.

Try some or all option to see will that help?

Thanx.
Vinay

http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay

vdharipally SSC Journeyman Points: 81 More actions · Answer 5

Hi All,

Thanks everyone for posting your great suggestions out of excellent experiences. I also had faced the same problem and stuck for 1 day . Changed the query time out and DB owner and Agent related jobs owner ship to "SA".