replication cause blocking or deadlock?

Hi all,
I have a question about the replication that induce any blocking or deadlock in SQL? the scenario is server A will have merge
replication with server B on database D1 with table T1, concurrently with a transactional replication with server B on database D2
with T2 (push subscription). Since i found the merge replication is running ok, but when the distribution agent running for
trasaactional replication, a blocking is occur. Any idea of this issues? thanks
Hi,
Thanks for your post.
From your descriptions, I understood that your SQL Agent will raise a
blocking while your merge replication and transactional replication happens
at the same time . Have I understood you? Correct me if I was wrong.
First of all, please know that replication issues tend to be very complex
and hard to troubleshoot in newsgroups. If you need further assistance, I
recommend that you open a Support incident with Microsoft Product Support
Services (PSS) so that a dedicated Support Professional can work with you
in a more timely and efficient manner. If you need any help in this regard,
please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
For now, If you'd still like to continue working via the newsgroup, I want
to set your expectations that the issue might take a long time to narrow
down. During the course of troubleshooting, we may redirect you to PSS if
required.
Generally speaking, blocking or deadlocking occurs when the users on the
system were not freeing up resources, perhaps because they were holding
long running transactions. The user transactions were getting locks on
resources (rows, pages, and/or tables) that the snapshot agent needed
access to and the snapshot agent could not continue without those locks,
and vice versa. The snapshot agent was chosen as a deadlock victim. When
you tried the snapshot agent on a less busy system it was being blocked
instead of deadlocked but this still resulted in the snapshot agent failing
as it now timed out.
So here are some suggestions prevent from the deadlock
1. Running the snapshot agent during a time when few or no users are on the
system. This will reduce but not eliminate the change of a deadlock or
blocking. You can change the QueryTimeout to something higher than the
default of 300 seconds also.
2. Running the snapshot agent with the concurrent snapshot option. This
will reduce but not eliminate the change of a deadlock or blocking. The
StartQueueTimeout can be set to 0 when using this option for an indefinite
wait.
3. Manually synchronizing the subscription during a time when no users are
on the system. You ended up putting the publication database into single
user mode then executing the snapshot agent. This prevented both
deadlocking and blocking.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||HI Mingqing Cheng,
thanks for your reply, actually the snapshot agent is ok when running but when i start to synchronizing manually, the blocking
occur. It is quite strange that the merge replication is work on Table T1 and transactional replication is work on another table T2
will induce the Blocking.
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message news:2eX0rUDrEHA.752@.cpmsftngxa06.phx.gbl...
> Hi,
> Thanks for your post.
> From your descriptions, I understood that your SQL Agent will raise a
> blocking while your merge replication and transactional replication happens
> at the same time . Have I understood you? Correct me if I was wrong.
> First of all, please know that replication issues tend to be very complex
> and hard to troubleshoot in newsgroups. If you need further assistance, I
> recommend that you open a Support incident with Microsoft Product Support
> Services (PSS) so that a dedicated Support Professional can work with you
> in a more timely and efficient manner. If you need any help in this regard,
> please let me know.
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
> For now, If you'd still like to continue working via the newsgroup, I want
> to set your expectations that the issue might take a long time to narrow
> down. During the course of troubleshooting, we may redirect you to PSS if
> required.
> Generally speaking, blocking or deadlocking occurs when the users on the
> system were not freeing up resources, perhaps because they were holding
> long running transactions. The user transactions were getting locks on
> resources (rows, pages, and/or tables) that the snapshot agent needed
> access to and the snapshot agent could not continue without those locks,
> and vice versa. The snapshot agent was chosen as a deadlock victim. When
> you tried the snapshot agent on a less busy system it was being blocked
> instead of deadlocked but this still resulted in the snapshot agent failing
> as it now timed out.
> So here are some suggestions prevent from the deadlock
> 1. Running the snapshot agent during a time when few or no users are on the
> system. This will reduce but not eliminate the change of a deadlock or
> blocking. You can change the QueryTimeout to something higher than the
> default of 300 seconds also.
> 2. Running the snapshot agent with the concurrent snapshot option. This
> will reduce but not eliminate the change of a deadlock or blocking. The
> StartQueueTimeout can be set to 0 when using this option for an indefinite
> wait.
> 3. Manually synchronizing the subscription during a time when no users are
> on the system. You ended up putting the publication database into single
> user mode then executing the snapshot agent. This prevented both
> deadlocking and blocking.
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Cweb,
Thanks for your further explaination!
To get a better picture of the problem we would like to reproduct the
issue on our end. To expedite the process, please provide us the detailed
reproduce steps, which, I believe, will make us closer and quicker to the
resolution.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||HI Mingqing Cheng,
thanks for ur help, the problem seems resolved. Just know that the distribution agent running on transactional replication
scheduled with continuously, when i ask the colleague change it to run hourly, then the blocking seems disappeared. I don't know the
agent schedule will induce the blocking? Anyway, thanks for ur help
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message news:wy6GdYTrEHA.1344@.cpmsftngxa06.phx.gbl...
> Hi Cweb,
> Thanks for your further explaination!
> To get a better picture of the problem we would like to reproduct the
> issue on our end. To expedite the process, please provide us the detailed
> reproduce steps, which, I believe, will make us closer and quicker to the
> resolution.
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi CWeb,
Thanks for your prompt updates letting me know the status of this issue.
Running agent hourly may have opportunity to release the resource so that
the block was also released or reduce the conflict of resource scrambling,
however, I am afraid it is just a workaround but we still cannot define the
root cause.
Anyway, it's great to hear it works fine for you and if you would love to
find the root cause of it, help me reproduce it on my side with your
scripts. Moreover, you could re-open this thread or make a new one whenever
you would like to further help on this issue. We are here to be of
assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!