Merge Agent is reporting deadlocks

Can anyone tell me what could be causing the following deadlock issues? I
have done a SQL Profiler trace which isn't picking up the Deadlock. The
subscription type is pull and there are more than 12 merge agents on the
Publisher
The process could not enumerate changes at the 'Publisher'.
Percent Complete: 0
The process could not enumerate changes at the 'Publisher'.
Repl Agent Status: 5
Percent Complete: 0
Category:NULL
Source: Merge Replication Provider
Number: -2147200999
Message: The process could not enumerate changes at the 'Publisher'.
Repl Agent Status: 3
Percent Complete: 0
Category:SQLSERVER
Source: SQLBDG01PA
Number: 1205
Message: Transaction (Process ID 94) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
Repl Agent Status: 3
Disconnecting from Publisher 'SQLBDG01PA'
Disconnecting from Subscriber 'EBIVCSA'
Disconnecting from Publisher 'SQLBDG01PA'
Disconnecting from Publisher 'SQLBDG01PA'
Disconnecting from Publisher 'SQLBDG01PA'
Disconnecting from Publisher 'SQLBDG01PA'
Disconnecting from Subscriber 'EBIVCSA'
Disconnecting from Subscriber 'EBIVCSA'
Disconnecting from Subscriber 'EBIVCSA'
Disconnecting from Subscriber 'EBIVCSA'
Disconnecting from Publisher 'SQLBDG01PA'
Disconnecting from Distributor 'SQLBDG01PA'
Hello,
You may want to enable 1204, 3605 and 1205 flag to see more information
about this deadlock:
The following article is for your reference:
832524 SQL Server technical bulletin - How to resolve a deadlock
http://support.microsoft.com/?id=832524
Best 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.
--
>Thread-Topic: Merge Agent is reporting deadlocks
>thread-index: AcYE+kfsejv9d52cQuKGAjAGyxI8Wg==
>X-WBNR-Posting-Host: 159.99.4.20
>From: =?Utf-8?B?U3lkbmV5?= <hsc@.newsgroup.nospam>
>Subject: Merge Agent is reporting deadlocks
>Date: Mon, 19 Dec 2005 16:14:02 -0800
>Lines: 36
>Message-ID: <1CF81079-5E0F-46A6-8C0C-2FFE03906D20@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.replication:67673
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>Can anyone tell me what could be causing the following deadlock issues? I
>have done a SQL Profiler trace which isn't picking up the Deadlock. The
>subscription type is pull and there are more than 12 merge agents on the
>Publisher
>The process could not enumerate changes at the 'Publisher'.
>Percent Complete: 0
>The process could not enumerate changes at the 'Publisher'.
>Repl Agent Status: 5
>Percent Complete: 0
>Category:NULL
>Source: Merge Replication Provider
>Number: -2147200999
>Message: The process could not enumerate changes at the 'Publisher'.
>Repl Agent Status: 3
>Percent Complete: 0
>Category:SQLSERVER
>Source: SQLBDG01PA
>Number: 1205
>Message: Transaction (Process ID 94) was deadlocked on lock resources with
>another process and has been chosen as the deadlock victim. Rerun the
>transaction.
>Repl Agent Status: 3
>Disconnecting from Publisher 'SQLBDG01PA'
>Disconnecting from Subscriber 'EBIVCSA'
>Disconnecting from Publisher 'SQLBDG01PA'
>Disconnecting from Publisher 'SQLBDG01PA'
>Disconnecting from Publisher 'SQLBDG01PA'
>Disconnecting from Publisher 'SQLBDG01PA'
>Disconnecting from Subscriber 'EBIVCSA'
>Disconnecting from Subscriber 'EBIVCSA'
>Disconnecting from Subscriber 'EBIVCSA'
>Disconnecting from Subscriber 'EBIVCSA'
>Disconnecting from Publisher 'SQLBDG01PA'
>Disconnecting from Distributor 'SQLBDG01PA'
>
|||Thank you for that information Peter,
It helped me isolate where the deadlock is. Below is the output from the log
which shows that the deadlock is occurring in some SQL stored procedures. An
update is being done on one of our tables nad this is where it occurrs. This
is a major problem for us as the site we are supporting cannot synchronise
their servers at all. There are 12 servers to synchronise but only 8 can be
done and then they all start to fail one at a time. If anyone has any other
information about how to get around this problem it would be greatly
appreciated.
Node:1
KEY: 9:1977058079:7 (ee0128400385) CleanCnt:1 Mode: U Flags: 0x0
Grant List 0::
Owner:0x42bdf5c0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:147 ECID:0
SPID: 147 ECID: 0 Statement Type: SELECT Line #: 23
Input Buf: RPC Event: sp_MSenumchanges;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:101 ECID:0 Ec0x5D4DB530)
Value:0x51ad0560 Cost0/1E8)
Node:2
KEY: 9:1977058079:1 (95002952f11f) CleanCnt:1 Mode: X Flags: 0x0
Grant List 0::
Owner:0x5a33bdc0 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:101 ECID:0
SPID: 101 ECID: 0 Statement Type: UPDATE Line #: 935
Input Buf: RPC Event: sp_upd_BE8D5D46BE0642C56A093B6C6808492A;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:147 ECID:0 Ec0x5E7E1530)
Value:0x42bdc420 Cost0/0)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:147 ECID:0 Ec0x5E7E1530)
Value:0x42bdc420 Cost0/0)
End deadlock search 485 ... a deadlock was found.
"Peter Yang [MSFT]" wrote:

> Hello,
> You may want to enable 1204, 3605 and 1205 flag to see more information
> about this deadlock:
> The following article is for your reference:
> 832524 SQL Server technical bulletin - How to resolve a deadlock
> http://support.microsoft.com/?id=832524
> Best 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.
>
> --
>
|||Hello,
Based on my research, the new SQL Server build 818 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.
Best 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.
--
>Thread-Topic: Merge Agent is reporting deadlocks
>thread-index: AcYGeQ48jpTy5VEoQhSZsrR+3H87vw==
>X-WBNR-Posting-Host: 159.99.4.20
>From: =?Utf-8?B?U3lkbmV5?= <hsc@.newsgroup.nospam>
>References: <1CF81079-5E0F-46A6-8C0C-2FFE03906D20@.microsoft.com>
<VdGdTKTBGHA.1236@.TK2MSFTNGXA02.phx.gbl>
>Subject: RE: Merge Agent is reporting deadlocks
>Date: Wed, 21 Dec 2005 13:54:02 -0800
>Lines: 127
>Message-ID: <EED67217-642D-4E1C-808D-E4E51CD66D2C@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.replication:67722
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>Thank you for that information Peter,
>It helped me isolate where the deadlock is. Below is the output from the
log
>which shows that the deadlock is occurring in some SQL stored procedures.
An
>update is being done on one of our tables nad this is where it occurrs.
This
>is a major problem for us as the site we are supporting cannot
synchronise
>their servers at all. There are 12 servers to synchronise but only 8 can
be
>done and then they all start to fail one at a time. If anyone has any
other
>information about how to get around this problem it would be greatly
>appreciated.
>Node:1
>KEY: 9:1977058079:7 (ee0128400385) CleanCnt:1 Mode: U Flags: 0x0
> Grant List 0::
> Owner:0x42bdf5c0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:147
ECID:0
> SPID: 147 ECID: 0 Statement Type: SELECT Line #: 23
> Input Buf: RPC Event: sp_MSenumchanges;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: X SPID:101 ECID:0 Ec0x5D4DB530)
>Value:0x51ad0560 Cost0/1E8)
>Node:2
>KEY: 9:1977058079:1 (95002952f11f) CleanCnt:1 Mode: X Flags: 0x0
> Grant List 0::
> Owner:0x5a33bdc0 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:101
ECID:0[vbcol=seagreen]
> SPID: 101 ECID: 0 Statement Type: UPDATE Line #: 935
> Input Buf: RPC Event: sp_upd_BE8D5D46BE0642C56A093B6C6808492A;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: S SPID:147 ECID:0 Ec0x5E7E1530)
>Value:0x42bdc420 Cost0/0)
>Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: S SPID:147 ECID:0 Ec0x5E7E1530)
>Value:0x42bdc420 Cost0/0)
>End deadlock search 485 ... a deadlock was found.
>
>"Peter Yang [MSFT]" wrote:
rights.[vbcol=seagreen]
I[vbcol=seagreen]
the[vbcol=seagreen]
with
>