Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Process could not execute 'sp_replcmds' Expand / Collapse
Author
Message
Posted Tuesday, December 8, 2009 10:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 8:02 AM
Points: 28, Visits: 623
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
Posted Friday, December 11, 2009 6:55 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:23 AM
Points: 3,315, Visits: 6,723
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
Posted Monday, December 14, 2009 4:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 8:02 AM
Points: 28, Visits: 623
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
Posted Friday, January 8, 2010 11:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 24, 2014 9:16 AM
Points: 247, Visits: 3,868
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
Posted Monday, January 18, 2010 2:14 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 8:44 PM
Points: 1,520, Visits: 739
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
Posted Wednesday, May 2, 2012 12:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 9:13 AM
Points: 1, Visits: 26
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 »

Add to briefcase

Permissions Expand / Collapse