June 26, 2007 at 6:49 pm
I just got a new job and they claim that some of their (SS 2000 SP4) replication processes get blocked by users. They've charged me with building a script that will catch the spids red handed and kill them. My question is this: what's the best and safest way to tell if replication is running and if something if blocking one of it's processes?
June 27, 2007 at 7:45 am
Can you give any more detail ? What part of replication is getting blocked ? I would think it's snapshots contending for the same tables as users. Perhaps you change the timing of replication jobs.
June 28, 2007 at 1:46 am
Hi,
Has quite a bit to do with the type of replication. We have user replication running on our live production website..this transactional repliaction.
Also what sort of tasks are the users running, if they are large transactions then you may get queue issues where there is a backlog of records waiting to be applied.
Do you have any more detailed info.
Graeme
June 28, 2007 at 11:57 am
Sorry I was so vague. First of all, the is 2000 transactional replication. In the meantime, I've been gathering some stats from a script that has been running. This script, among other things, finds all spids that are being blocked by another spid and then it pulls in the inputbuffer. I noticed a couple that seem tied into replication. One is the following:
sp_MSupd_tbl_Ev;1
I'm assuming the above is part of replication (because this table tbl_Ev is replicated), but I can't prove that as I don't how to google for this.
The only things replication-related that was blocked was writing out the job history which happened quite frequently. Here's a sample statement that was blocked:
EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = 0x0AB608F46F9C484CA1CC1F76382B5F21, @step_id = 1, @sql_message_id = 50000, @sql_severity = 16, @run_status = 0, @run_date = 20070621, @run_time = 135003, @run_duration = 1, @operator_id_emailed = 0, @o
Is this a big deal? Why would writing to job history get blocked??
June 29, 2007 at 12:47 am
Well that procedure is certainly an replication update procedure.
How big is tbl_Ev.
Is it being accessed all the time ?
What is its function?
Graeme
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply