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

Question

Post reply

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

Emmanuel-484799

Old Hand

Points: 329
More actions
September 1, 2007 at 5:54 pm

#95392

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

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

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

Jo Pattyn SSC-Dedicated Points: 32341 More actions · Answer 1

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

Emmanuel-484799 Old Hand Points: 329 More actions · Answer 2

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: 32341 More actions · Answer 3

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 More actions · Answer 4

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: 32341 More actions · Answer 5

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

Aditya-474792 SSC Journeyman Points: 83 More actions · Answer 6

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

ps. One Orange Chip Points: 29422 More actions · Answer 7

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 More actions · Answer 8

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 SSCrazy Eights Points: 8054 More actions · Answer 9

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 More actions · Answer 10

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 More actions · Answer 11

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

Regards

Nneka

cwilliams-1119036 Old Hand Points: 322 More actions · Answer 12

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 More actions · Answer 13

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.