Re: Deadlock between Distribution Agent and Distribution Agent Cleanup

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



.