The process could not execute ''sp_replcmds'' on PRODUCTION

  • Emmanuel-484799

    Old Hand

    Points: 329

    Hi Champs,

    Thursday i run DDREINDEX on my Production Server, Next day i went to office i find out that Replication is not working, It Marked as RED Cross on Replication Monitor.

    Production Server Transactional Replication to Subscriber Server (Reporting Server)

    In the Log Reader Agent I seen the Message The process could not execute 'sp_replcmds' on 'servername'

    Actually am new to this Replication, Now what can i do Please help me.

    I Stop the Agent and Re-run but still the same error  message is coming

    Please help me it will be very greatefull.

    Thanks

    Emmanuel

    emmanuel_canagallas@hotmail.co.uk

     

  • Jo Pattyn

    SSC-Dedicated

    Points: 31258

    It could be timing out. You can change batchsize & timeout in the replication agent profiles.

  • Emmanuel-484799

    Old Hand

    Points: 329

    Hi I Changed the timeout from 10 to 40, but still its giving same error message.

    and my Log file is also filling before is like 2GB on (Thursday) now its increases to 18GB

    I think all the Replication Transactions are stored in the Log file.

    Every 15min Transaction Log Backup is running and Once a Full backup at mid night.

    I really dont know what to do? Please can you help

    Thanks..

     

  • Jo Pattyn

    SSC-Dedicated

    Points: 31258

    I would start with reducing the batch size.

    It was needed at work to reduce it from 500 to 100 because our subscriber was slower in IO

    Found the kb:

    http://support.microsoft.com/kb/811030

  • Emmanuel-484799

    Old Hand

    Points: 329

    Ok i changed the batch size to 100.

    I run the Agents am wating for the Result.

    mean time What about the Log file its reached 18GB what can i do with it.

    Cheers..

     

  • Jo Pattyn

    SSC-Dedicated

    Points: 31258

    You can perform a transaction log backup. It automatically truncates the inactive portion of the transaction log.

  • Aditya-474792

    SSC Journeyman

    Points: 83

    I think we cannot truncate transaction logs until log reader agent moves the transactions to teh subscriber

  • ps.

    One Orange Chip

    Points: 29250

    Aditya (1/19/2009)


    I think we cannot truncate transaction logs until log reader agent moves the transactions to teh subscriber

    to the distribution database to be more specific.



    Pradeep Singh

  • stamic

    SSC Eights!

    Points: 867

    Reducing the batch size did not work for me on this issue, however, by reapplying the snapshot to the subscriber I was able to get the log reader to function without the error. I assume this is because the bulk of the records in the log on the publisher that were marked for replication were brought over with the snapshot. Regardless, wanted to share this info on this subject.

  • Graeme100

    SSCertifiable

    Points: 7875

    Yes applying a new snapshot is a last resort..

    In your situation, you needed to let the logreader "catch up" as there were many transactions in the TLog. This may have meant stopping activity on your database...perhaps that would not have been viable.

    Regards

    Graeme

  • gardengnome

    SSC Rookie

    Points: 47

    Sorry to add to an old post but hopefully it helps.

    I had the same problem today after REORGANIZING a ton of heavily fragmented indexes (rebuild not an option).

    To fix I had to go into Enterprise Mgr..... find the agent under replication->log reader agent

    Right click, choose agent profiles.

    This allowed me to create a new profile with a larger timeout value and i had to also set ReadBatchSize to 1

    After doing this replication is now running again, it just took longer than normal to catch back up but this has fixed the problem for me. I will change the ReadBatchSize parameter back to 500 after a few hours but thought I'd share my experience with this issue.

    Thanks.

  • Nneka Ilene

    SSC Journeyman

    Points: 95

    Thank you. Your answer just saved me three hours. It worked!

    Regards

    Nneka

  • cwilliams-1119036

    SSC Veteran

    Points: 212

    Sorry this is such a late post but we just started having this same issue on Sundays when our REORG's run. Any ideas on why reorganizing causes this to happen? I will try your fix next time unless I can find what is causing this to happen and can prevent it.

  • mark.pointon

    SSC Enthusiast

    Points: 190

    HI, I'd thought that I'd add my experience on this. We experienced the same issue with the log reader agent in Sql 2000, caused by a now outage over the weekend. Instead of amending the -readbatchsize to 1, I amend from 500 to 100, which also worked. I suppose it depends on the severity of the backlog. So you don't need to reduce all the way down to single batch sizes.

Viewing 14 posts - 1 through 14 (of 14 total)

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