May 10, 2013 at 6:16 am
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.
May 10, 2013 at 7:25 am
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.
May 10, 2013 at 9:57 am
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.
May 10, 2013 at 10:03 am
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.
May 10, 2013 at 10:08 am
How you are validating from the SQL Server Error Log that it is replication monitoring causing the deadlock?
May 10, 2013 at 11:18 am
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.
May 10, 2013 at 11:35 am
SQL server error log will also be showing which resource it is conflicting with causing deadlock.
May 10, 2013 at 11:44 am
I know what is causing the deadlock, the question is 'why' and 'how'
May 10, 2013 at 11:52 am
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?
May 10, 2013 at 1:08 pm
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
May 10, 2013 at 2:00 pm
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