Process could not execute 'sp

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Replication » Process could not execute 'sp_replcmds'

Process could not execute 'sp_replcmds'

Rate Topic

Display Mode

Topic Options

Author

Message

pdchandran

Posted Tuesday, December 08, 2009 10:21 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, January 17, 2013 2:08 PM
Points: 28, Visits: 622

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.

Post #830828

Roy Ernest

Posted Friday, December 11, 2009 6:55 AM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, June 13, 2013 11:29 AM
Points: 3,280, Visits: 6,626

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

Post #832894

pdchandran

Posted Monday, December 14, 2009 4:58 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, January 17, 2013 2:08 PM
Points: 28, Visits: 622

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.

Post #833723

José.Cruz

Posted Friday, January 08, 2010 11:31 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 240, Visits: 3,377

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

Post #844568

Vinay Thakur-585143

Posted Monday, January 18, 2010 2:14 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 4:05 PM
Points: 1,514, Visits: 720

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

Post #849399

vdharipally

Posted Wednesday, May 02, 2012 12:39 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 01, 2013 12:11 PM
Points: 1, Visits: 20

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

Post #1294119

« Prev Topic | Next Topic »

Permissions