Re: Deadlock between Distribution Agent and Distribution Agent Cleanup

Kevin,

This is not 'extreme' for me but the fact that those deadlocks are
happening makes me nervous in case the activity expands for more
extended periods. This is something that I would like to avoid if at
all possible.

and you are correct it fails, then retrys and if the 'high' activity
period some how subsides a bit it succeeds. I thought those lock hints
were pretty safe to avoid such situations but apparently I was wrong.

Thanks for the feedback.

-Noel
Sr DBA


Kevin3NF wrote:
I've seen this a lot, since they are both hitting the same repl table at the
same time, but I've never seen it fail/deadlock for extended periods of
time. If your agent failing, then succeeding?

--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm

Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com


<zerg2k@xxxxxxxxx> wrote in message
news:1166732530.463614.305580@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I am experiencing this problem. Deadlock of these two M$ stored
procedures :

sp_MSget_repl_commands (Executed by the Distribution Agent --pull
subscriber ) and
sp_MSdistribution_cleanup (Executed by the Distribution Agent Cleanup
job)

the offending queries are :

From sp_MSdistribution_cleanup:

DELETE MSrepl_commands WITH (PAGLOCK) where
publisher_database_id = @publisher_database_id and
xact_seqno <= @max_xact_seqno

From sp_MSget_repl_commands:
select @max_xact_seqno = max(xact_seqno) from MSrepl_commands
(READPAST)
where
publisher_database_id = @publisher_database_id and
command_id = 1 and
type <> -2147483611

I searched this and other groups and no convincing answer was posted.
Is there anyone experiencing this problem ? if so what did you do to
"resolve" it (not to decrease its frequency)

Thanks in Advance.

-Noel
Sr. DBA


.

Loading