Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Process could not execute 'sp_replcmds'


Process could not execute 'sp_replcmds'

Author
Message
pdchandran
pdchandran
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
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.
Roy Ernest
Roy Ernest
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2496 Visits: 6853
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
pdchandran
pdchandran
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
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.
José.Cruz
José.Cruz
SSC Veteran
SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)

Group: General Forum Members
Points: 256 Visits: 4051
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
Vinay Thakur-585143
Vinay Thakur-585143
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1644 Visits: 771
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
vdharipally
vdharipally
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 27
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".
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search