Replication Monitor Deadlocking

  • Hello,

    Both in my SQL Server logs and RedGate SQL Monitor is showing the SQL Server Replication Monitor causing deadlocks. It doesn't seem to be affecting our user databases, which is good.

    So my question is this, has anyone experienced this issue before, and if so, what did you do to resolve it besides not opening the Replication Monitor?

    Thank you for your answers.

  • Could you please share SQL server error log?

    Never seen Replication Monitor causing dead lock but if you share your error log, it might help understanding actual issue.

  • Sure, here is a snippet of the SQL Server Error Log:

    Deadlock encountered .... Printing deadlock information

    ResType:ExchangeId Stype:'AND' SPID:92 BatchID:0 ECID:7 TaskProxy:(0x0000000159F89AD0) Value:0x3f6b948 Cost:(0/10000)

    Port: 0x00000000E20A8BD0 Xid Slot: 7, Wait Slot: 17, Task: 0x0000000003F6B948, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0

    Node:4

    I will post the results from SQL Monitor shortly.

  • I run Merge replication and the Replication agents checkup job has deadlocks occasionally.

    Since it runs every ten minutes, I do not worry too much about it.

  • How you are validating from the SQL Server Error Log that it is replication monitoring causing the deadlock?

  • I'm validating the deadlock through SQL Monitor and comparing the times with both the SQL Server Logs and SQL Monitor logs. Below is a snippet from SQL Monitor:

    Deadlocked processes

    SPID - 114

    Victim

    Lock details

    Statement type

    Port: 0x00000005BDC0F760 Xid Slot: 31, Wait Slot: 7, Task: 0x000000000587CE08, (Producer), Exchange Wait Type: e_waitPipeNewRow, Merging: 1

    SPID - 114

    Replication Monitor

    XXXXXXXXXXX-LTP

    Again, this is not affecting our user databases in any way. I would like to find the route cause, and if someone else ever experienced this when opening SQL Server Replication Monitor.

  • SQL server error log will also be showing which resource it is conflicting with causing deadlock.

  • I know what is causing the deadlock, the question is 'why' and 'how'

  • What I was trying to ask that when ever a deadlock happens there are always 2 parties involved. Do by any chance you know the second party?

  • Neeraj Dwivedi (5/10/2013)


    What I was trying to ask that when ever a deadlock happens there are always 2 parties involved. Do by any chance you know the second party?

    Not always. Could be 1 (intra-query parallelism deadlock), or 3 processes or more.

    Based on the wait type of e_waitPipeGetRow and the fact that the SPIDs are the same this one looks like it might be an intra-query parallelism deadlock. These are usually caused by bugs in the Database Engine itself and applying the latest Service Pack or CU may correct the problem, but maybe not.

    One way to address problems like this is to add query hint MAXDOP 1 to the specific query having the problem. Unfortunately since this is emanating out of code built into SQL Server Replication subsystem that will not be an option. You could look into plan guides but the bar would be high to get that right, I think. If the Replication Monitor is able to recover from the problem by restarting it and it is not affecting normal operations you might be best off ignoring it.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Not always. Could be 1 (intra-query parallelism deadlock), or 3 processes or more.

    Based on the wait type of e_waitPipeGetRow and the fact that the SPIDs are the same this one looks like it might be an intra-query parallelism deadlock. These are usually caused by bugs in the Database Engine itself and applying the latest Service Pack or CU may correct the problem, but maybe not.

    One way to address problems like this is to add query hint MAXDOP 1 to the specific query having the problem. Unfortunately since this is emanating out of code built into SQL Server Replication subsystem that will not be an option. You could look into plan guides but the bar would be high to get that right, I think. If the Replication Monitor is able to recover from the problem by restarting it and it is not affecting normal operations you might be best off ignoring it.

    From looking at the profiler trace, the deadlock seems to be caused when the 'EXEC master.dbo.xp_readerrorlog' statement is being executed from the Replication Monitor. I never seen anything like this before. In the very near future, I would like to at least upgrade the service pack level to SQL Server 2008R2, Service Pack #2. We are currently running 10.50.2811 which is CU#6 for SQL Server 2008 R2 SP1

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply