Process could not execute 'sp_replcmds'

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

  • 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

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

  • 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

  • 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

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

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

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