Hi, more or less once a day I receive this message from the merge
replication, because is continuous suddenly stops. I try to put an extra step
on the job to restart it but is ignored. Any clues why my step is ignored or
why this problem is happening?
cheers , Error:
The process could not enumerate changes at the 'Subscriber'.
(Source: Merge Replication Provider (Agent); Error number: -2147200999)
------
Transaction (Process ID 87) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
(Source: SUMSTR (Data source); Error number: 1205)
------
The process was successfully stopped.
(Source: Merge Replication Provider (Agent); Error number: -2147200999)
------
If you have large numbers of subscribers you might want to limit concurrent
processing or perhaps schedule your merge agents as opposed to running them
simultaneously or continuously.
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
"Salvador" <Salvador@.discussions.microsoft.com> wrote in message
news:BA09357D-8EE6-42A4-97C3-72384153AD02@.microsoft.com...
> Hi, more or less once a day I receive this message from the merge
> replication, because is continuous suddenly stops. I try to put an extra
step
> on the job to restart it but is ignored. Any clues why my step is ignored
or
> why this problem is happening?
> cheers , Error:
> The process could not enumerate changes at the 'Subscriber'.
> (Source: Merge Replication Provider (Agent); Error number: -2147200999)
> ----
--
> Transaction (Process ID 87) was deadlocked on lock resources with another
> process and has been chosen as the deadlock victim. Rerun the transaction.
> (Source: SUMSTR (Data source); Error number: 1205)
> ----
--
> The process was successfully stopped.
> (Source: Merge Replication Provider (Agent); Error number: -2147200999)
> ----
Showing posts with label deadlock. Show all posts
Showing posts with label deadlock. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
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 Ec
0x5D4DB530)
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
>
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 Ec
0x5D4DB530)Value:0x51ad0560 Cost
0/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 Ec
0x5E7E1530)Value:0x42bdc420 Cost
0/0)Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:147 ECID:0 Ec
0x5E7E1530)Value:0x42bdc420 Cost
0/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 Ec
0x5D4DB530)>Value:0x51ad0560 Cost
0/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 Ec
0x5E7E1530)>Value:0x42bdc420 Cost
0/0)>Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: S SPID:147 ECID:0 Ec
0x5E7E1530)>Value:0x42bdc420 Cost
0/0)>End deadlock search 485 ... a deadlock was found.
>
>"Peter Yang [MSFT]" wrote:
rights.[vbcol=seagreen]
I[vbcol=seagreen]
the[vbcol=seagreen]
with
>
Merge agent fails due to deadlock
Hi:
I met a problem this morning. the merge replication agen stopped, the
error message shows:
The process could not enumerate changes at the 'Publisher'.
(Source: Merge Replication Provider (Agent); Error number: -2147200999)
-----
Transaction (Process ID 79) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
(Source: XXXXXX (Data source); Error number: 1205)
The process was successfully stopped.
(Source: Merge Replication Provider (Agent); Error number: -2147200999)
I never met this issue before, so please help
1) What may cause this issue?
2) How I could identify which process cause the deadlock issue?
3) Is there any way to log more information to identify what resource
is deadlock, and by who.
Thanks
Yong
Yong,
have a look at enabling traceflags 1204, 1205, 3605 to get more info. Don't
forget to turn off afterwards. Alternatively if you have sql server 2005,
the profiler shows the deadlock tree graphically.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
I met a problem this morning. the merge replication agen stopped, the
error message shows:
The process could not enumerate changes at the 'Publisher'.
(Source: Merge Replication Provider (Agent); Error number: -2147200999)
-----
Transaction (Process ID 79) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
(Source: XXXXXX (Data source); Error number: 1205)
The process was successfully stopped.
(Source: Merge Replication Provider (Agent); Error number: -2147200999)
I never met this issue before, so please help
1) What may cause this issue?
2) How I could identify which process cause the deadlock issue?
3) Is there any way to log more information to identify what resource
is deadlock, and by who.
Thanks
Yong
Yong,
have a look at enabling traceflags 1204, 1205, 3605 to get more info. Don't
forget to turn off afterwards. Alternatively if you have sql server 2005,
the profiler shows the deadlock tree graphically.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
Subscribe to:
Posts (Atom)