Deadlock between Distribution Agent and Distribution Agent Cleanup

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
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@.yahoo.com> wrote in message
news:1166732530.463614.305580@.i12g2000cwa.googlegr oups.com...
>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 :
>
> DELETE MSrepl_commands WITH (PAGLOCK) where
> publisher_database_id = @.publisher_database_id and
> xact_seqno <= @.max_xact_seqno
> 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
>
|||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:[vbcol=seagreen]
> 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@.yahoo.com> wrote in message
> news:1166732530.463614.305580@.i12g2000cwa.googlegr oups.com...