Deadlock on merge agent

Hi,
I found deadlock error a week ago, and set up the 1024 traceflag as you
advised.
It caught one deadlock, but I'm really confused.
The two nodes of the deadlock are:
sp_MSmakegeneration
and one stored procedure which find a specific record by key and
update it.
This stored procedure is called in a batch process, but it will
commit the transaction after each call. And I have no any idea what is the
potential conficit with the sp_MSmakegeneration.
Please help.
Thanks
Yong
Quite often this deadlock is transient. If it is not you might want to limit
the number of concurrent merge agents.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Yong Zhang" <yongzhang@.newsgroup.nospam> wrote in message
news:eSTcrkBQGHA.3944@.tk2msftngp13.phx.gbl...
> Hi,
> I found deadlock error a week ago, and set up the 1024 traceflag as you
> advised.
> It caught one deadlock, but I'm really confused.
> The two nodes of the deadlock are:
> sp_MSmakegeneration
> and one stored procedure which find a specific record by key
> and update it.
> This stored procedure is called in a batch process, but it will
> commit the transaction after each call. And I have no any idea what is the
> potential conficit with the sp_MSmakegeneration.
> Please help.
> Thanks
> Yong
>
|||Hello,
As I know, SQL Server 2000 build 818 and later changed how the Merge Agent
locks records on the Publisher while it is synchronizing changes to the
subscriber. This new design change might cause the Merge Agent to Deadlock
with the Update from the application. We discovered we can "tune" the Merge
agent to lock a smaller number of records and hopefully avoid the
deadlocking.
The SQL Server help topic below describe how to create a new Merge Agent
Profile. In the new profile change the DownloadReadChangesPerBatch setting
from the default setting of 100 to 25. This will lock a smaller number of
records per batch while synchronizing. We believe 25 is a good compromise
but it may need to be adjusted.
See SQL Server Help Topics:
- Merge Agent Profile
- How to create a replication agent profile (Enterprise Manager)
Another the work around is to run the Merge Agent every minute instead of
continuously so when it fails with a deadlock, the Agent will automatically
restart.
Please rest assured this issue has been routed to the proper channel. If
there is any update on this, we will let you know. However, it may take
some time and we appreciate your patience.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Reply-To: "Yong Zhang" <yongzhang@.usadiscounters.net>
>From: "Yong Zhang" <yongzhang@.newsgroup.nospam>
>Subject: Deadlock on merge agent
>Date: Sun, 5 Mar 2006 00:57:30 -0500
>Lines: 21
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>X-RFC2646: Format=Flowed; Original
>Message-ID: <eSTcrkBQGHA.3944@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: ip68-10-6-136.hr.hr.cox.net 68.10.6.136
>Path: TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msft ngp13.phx.gbl
>Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.replication:69732
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>Hi,
> I found deadlock error a week ago, and set up the 1024 traceflag as
you
>advised.
> It caught one deadlock, but I'm really confused.
> The two nodes of the deadlock are:
> sp_MSmakegeneration
> and one stored procedure which find a specific record by key
and
>update it.
> This stored procedure is called in a batch process, but it will
>commit the transaction after each call. And I have no any idea what is the
>potential conficit with the sp_MSmakegeneration.
> Please help.
> Thanks
> Yong
>
>

deadlock in agent job

I have a job with a single t-sql step. The tsql executes a stored proc that
occasionally deadlocks.
I know i can't trap the deadlock in the stored proc, but I'd like to trap
the deadlock in the agent job, and retry the stored proc.
What is the best way to handle this?
-Rand yThis is normal behavior. A trigger is executed for each statement that cause
s
the trigger to fire and not for each row affected by the statement. See
"Multirow Considerations" in BOL.
AMB
"Randy" wrote:

> I have a job with a single t-sql step. The tsql executes a stored proc th
at
> occasionally deadlocks.
> I know i can't trap the deadlock in the stored proc, but I'd like to trap
> the deadlock in the agent job, and retry the stored proc.
> What is the best way to handle this?
> -Rand y
>
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> This is normal behavior. A trigger is executed for each statement that cau
ses
> the trigger to fire and not for each row affected by the statement. See
> "Multirow Considerations" in BOL.
>
> AMB
> "Randy" wrote:
>|||What about increasing "Retry attempts" in the advanced tab when creating or
modifing the job step.
AMB
"Randy" wrote:

> I have a job with a single t-sql step. The tsql executes a stored proc th
at
> occasionally deadlocks.
> I know i can't trap the deadlock in the stored proc, but I'd like to trap
> the deadlock in the agent job, and retry the stored proc.
> What is the best way to handle this?
> -Rand y
>
>

deadlock errors

I'm setting up a push merge replication, after the initial merge agent completes without errors I move the subscriber from a wired connection to a wireless connection. At this point I get an error…

The schema script …Program Files\Microsoft SQL Server\MSSQL\REPLDATA\unc\[table]_93.sch' could not be propagated to the subscriber.

(Source: Merge Replication Provider (Agent); Error number: -2147201001)

Cannot drop the table '[table]' because it is being used for replication.

(Source: dfdmfvltgh09 (Data source); Error number: 3724)

After this error the initial merge agent kicks off again trying to apply the initial snapshot where I then get the following deadlock errors.

Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

(Source: dfdmfvltgh09 (Data source); Error number: 1205)

The process could not deliver the snapshot to the Subscriber.

(Source: Merge Replication Provider (Agent); Error number: -2147201001)

All are getting set up on sql 2000 sp4 on a push.We are trying to set up 10 units that all are pointing to the same dlink router model dwl-2100AP.5 are working the other 5 are coming up with this error.Anybody have any ideas what could be causing this.I don’t mind the getting cut off due to poor network connectivity, I really want to stop the initial merge agent from kicking off after it’s already completed.

Thank you in advanced,

Pauly C

Let's tackle the snapshot failure first. Are you saying merge is trying to apply the snapshot twice, once on the wired network and once on the wireless network? Are these two networks on the same domain?

|||

Yes, the snapshot completes without errors, and replication works, changes get propergated back and forth between the subscriber and publisher, then when they move the subscriber to a wireless connection the first error occurs and the initial merge agent starts again.After applying some of the snapshot it gets the deadlock error. and it keeps trying to apply the initial merge agaent over and over.

|||And yes they are on the same domain. Somebody told me they were able to ping a machine that wasn't connected and actually got results. I did not see this with my own eyes so I'm not sure how true it is.

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...

Deadlock between Distribution Agent and Distribution Agent Cleanup

This is occurring regularly on SQL Server 2000 build 878.
The problem is a deadlock in the Distribution database. The Distribution
Agent spid is executing the SELECT statement below:
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
which is found in sp_MSget_repl_commands. It holds an Intent Shared page
lock on a data page in the MSrepl_commands table.
The Distribution Agent Cleanup spid is found to be running the command below:
DELETE MSrepl_commands WITH (PAGLOCK) where
publisher_database_id = @.publisher_database_id and
xact_seqno <= @.max_xact_seqno
located in the stored procedure sp_MSdelete_publisherdb_trans. This spid
holds an exclusive page lock on another data page in MSrepl_commands.
Both spids then attempt to obtain the same lock type on the page which is
locked by the other.
The Distribution Agent runs continuously and the Cleanup job is scheduled
for every 10 minutes. The Publication, Distribution and Subscription
databases are all on the same instance (3rd party vendor solution, not mine!)
in an active/active Win2003 cluster configuration. The articles are all
stored procedure executions.
Has anybody else seen this deadlock? Is it just a timing issue? Why is the
PAGLOCK hint used in sp_MSdelete_publisherdb_trans as above?
(I can't find any articles which correlate exactly to this problem)
Kind Regards
Andrew Pike
SQL Server DBA
Accenture UK
Do you have anonymous subscribers or named. With named subscribers the
distribution clean up agent cleans up more aggressively and you may see
problems like this when a subscriber has been offline for some time.
First off issue a select * from distribution.dbo.MSdistribution_status to
see how many undelivered vs delivered commands there are. If there are a
high number of delivered commands, I would stop the SQL Server Agent and run
the distribution clean up agent manually.
I can't comment on why the decision was made to implement the two types of
locks, but in general MS has done a lot of research to deliver optimal
performance. For example the 27 in sp_MSadd_repl_commands27 comes from
tests that they did to find the optimal number of commands to send to the
distribution database in a batch from the log reader agent. And yes, they
tested a range of commands to find which offered best performance.
It looks like the readpast is to prevent locking, and the page lock is to
prevent a table lock.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:CFB322D7-1D17-4064-AB36-6338A49C90A4@.microsoft.com...
> This is occurring regularly on SQL Server 2000 build 878.
> The problem is a deadlock in the Distribution database. The Distribution
> Agent spid is executing the SELECT statement below:
> 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
> which is found in sp_MSget_repl_commands. It holds an Intent Shared page
> lock on a data page in the MSrepl_commands table.
> The Distribution Agent Cleanup spid is found to be running the command
> below:
> DELETE MSrepl_commands WITH (PAGLOCK) where
> publisher_database_id = @.publisher_database_id and
> xact_seqno <= @.max_xact_seqno
> located in the stored procedure sp_MSdelete_publisherdb_trans. This spid
> holds an exclusive page lock on another data page in MSrepl_commands.
> Both spids then attempt to obtain the same lock type on the page which is
> locked by the other.
> The Distribution Agent runs continuously and the Cleanup job is scheduled
> for every 10 minutes. The Publication, Distribution and Subscription
> databases are all on the same instance (3rd party vendor solution, not
> mine!)
> in an active/active Win2003 cluster configuration. The articles are all
> stored procedure executions.
> Has anybody else seen this deadlock? Is it just a timing issue? Why is
> the
> PAGLOCK hint used in sp_MSdelete_publisherdb_trans as above?
> (I can't find any articles which correlate exactly to this problem)
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> Accenture UK
>