Deadlock details not available from some servers

rmcneish

I'm having an issue where some of my servers are not presenting the deadlock info in the details section of the alert.

A couple of servers of mine have a nice little UI with deadlock process information in the details tab of the deadlock alert. Other just show "No process information is available. Please check the Output tab for raw output." in the details tab, and I have to dig through the Output tab to figure out what is going on.

Not the end of the world because I can still see the offending processes, but it is much faster with the nice UI.

Brian Donahue

Hello,

I believe some types of deadlock that are internal to SQL Server may not print all of the information that a regular user deadlock does. What is in the raw output?

Brian Donahue

This is the sort of thing I'm thinking about: Deadlock related to parallelism.
http://www.sqlservercentral.com/Forums/ ... 391-1.aspx

AndyF

Brian, I'm also having this same issue on SQL Monitor 4. Trace flag 1222 only is enabled (I originally had trace flag 1204 enabled also, so turned this off, but I'm still encountering the issue). Most deadlock events are missing the Details (text "Error parsing deadlock information." is there in it's place, though the raw trace is in the Output window).

Below I've pasted an example of a deadlock for which Detail was present and an example for which Detail was absent. They are both for the same SQL Server instance.

(1) Example of deadlock for which Details was present:

deadlock-list
deadlock victim=process2d104c088
process-list
process id=process2d104c088 taskpriority=0 logused=0 waitresource=PAGE: 18:1:439491 waittime=3618 ownerId=44719310360 transactionname=SELECT lasttranstarted=2014-01-22T14:35:41.020 XDES=0x18df8d640 lockMode=S schedulerid=5 kpid=5724 status=suspended spid=226 sbid=3 ecid=11 priority=0 trancount=0 lastbatchstarted=2014-01-22T14:35:41.020 lastbatchcompleted=2014-01-22T14:34:40.163 clientapp=.Net SqlClient Data Provider hostname=myhost hostpid=4488 isolationlevel=read committed (2) xactid=44719310360 currentdb=18 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=adhoc line=1 stmtstart=82 sqlhandle=0x0200000043d79a03baa76f44da1a46ce02d1f1f23f70982d
SELECT MSG_MESSAGE, MSG_LINE, MSG_TEXT FROM MESSAGE WHERE MSG_TOORG = @string1 AND MSG_TOUSER = @string2 AND MSG_LEVEL = '00' AND MSG_STAGE < '90' ORDER BY MSG_PRIORITY ASC,MSG_DATECREATED ASC
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
inputbuf
process id=process807b1b88 taskpriority=0 logused=16948 waitresource=PAGE: 18:1:243993 waittime=3613 ownerId=44719310185 transactionname=UPDATE lasttranstarted=2014-01-22T14:35:40.947 XDES=0x2a106a3b0 lockMode=IX schedulerid=5 kpid=2100 status=suspended spid=224 sbid=3 ecid=0 priority=0 trancount=2 lastbatchstarted=2014-01-22T14:35:40.947 lastbatchcompleted=2014-01-22T14:35:40.820 clientapp=.Net SqlClient Data Provider hostname=myhost hostpid=21128 loginname=mylogin isolationlevel=read committed (2) xactid=44719310185 currentdb=18 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=adhoc line=1 stmtstart=176 sqlhandle=0x02000000becc3f119e34bae0788df72f263fda0fb5e70b78
UPDATE MESSAGE SET MSG_SUPERVISOR = @stringUP0,MSG_DATECLOSED = @datetimeUP1,MSG_STAGE = @stringUP2 WHERE MSG_MESSAGE = @string1
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
inputbuf
(@string1 varchar(10),@stringUP0 varchar(6),@datetimeUP1 datetime,@stringUP2 varchar(2))UPDATE MESSAGE SET MSG_SUPERVISOR = @stringUP0,MSG_DATECLOSED = @datetimeUP1,MSG_STAGE = @stringUP2 WHERE MSG_MESSAGE = @string1
resource-list
pagelock fileid=1 pageid=439491 dbid=18 objectname=mylivedb.dbo.MESSAGE id=lock1e2498800 mode=IX associatedObjectId=72057594092650496
owner-list
owner id=process807b1b88 mode=IX
waiter-list
waiter id=process2d104c088 mode=S requestType=wait
pagelock fileid=1 pageid=243993 dbid=18 objectname=mylivedb.dbo.MESSAGE id=lock144d16480 mode=S associatedObjectId=72057594092650496
owner-list
owner id=process2d104c088 mode=S
waiter-list
waiter id=process807b1b88 mode=IX requestType=wait


(2) Example of deadlock for which Details was missing:


deadlock-list
deadlock victim=process15b2502c8
process-list
process id=process15b2502c8 taskpriority=0 logused=0 waitresource=RID: 21:1:1294464:9 waittime=1112 ownerId=44717634254 transactionname=SELECT lasttranstarted=2014-01-22T14:33:08.543 XDES=0x2d0015b50 lockMode=S schedulerid=5 kpid=2756 status=suspended spid=155 sbid=2 ecid=0 priority=0 trancount=0 lastbatchstarted=2014-01-22T14:33:08.543 lastbatchcompleted=2014-01-22T14:33:08.543 clientapp=.Net SqlClient Data Provider hostname=myhost hostpid=19924 loginname=mylogin isolationlevel=read committed (2) xactid=44717634254 currentdb=21 lockTimeout=4294967295 clientoption1=671350816 clientoption2=128056
executionStack
frame procname=adhoc line=1 stmtstart=72 sqlhandle=0x020000006b67bd0efaff79dd1272dbb5082e8739153635c0
SELECT * FROM PRINT_QUEUE WHERE PRQ_ORG = @string1 AND PRQ_STAGE = '10' AND UPPER(PRQ_PRINTER) IN ('ZDESIGNER GK420D','SEND TO ONENOTE 2010','SAMSUNG ML-2855 SERIES PCL6','MICROSOFT XPS DOCUMENT WRITER','HP LASERJET 1020 (COPY 1)','FAX','\\STWK0186\MYP INV DELL') ORDER BY PRQ_DATECREATED ASC,PRQ_XDOC ASC
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
inputbuf
(@string1 varchar(4))SET ROWCOUNT 1 SELECT * FROM PRINT_QUEUE WHERE PRQ_ORG = @string1 AND PRQ_STAGE = '10' AND UPPER(PRQ_PRINTER) IN ('ZDESIGNER GK420D','SEND TO ONENOTE 2010','SAMSUNG ML-2855 SERIES PCL6','MICROSOFT XPS DOCUMENT WRITER','HP LASERJET 1020 (COPY 1)','FAX','\\STWK0186\MYP INV DELL') ORDER BY PRQ_DATECREATED ASC,PRQ_XDOC ASC
process id=process6c3288 taskpriority=0 logused=640 waitresource=KEY: 21:72057594093568000 (ce013c503a7c) waittime=1113 ownerId=44717634252 transactionname=UPDATE lasttranstarted=2014-01-22T14:33:08.543 XDES=0x280a543b0 lockMode=X schedulerid=3 kpid=5388 status=suspended spid=151 sbid=3 ecid=0 priority=0 trancount=2 lastbatchstarted=2014-01-22T14:33:08.543 lastbatchcompleted=2014-01-22T14:33:08.543 clientapp=.Net SqlClient Data Provider hostname=myhost hostpid=19924 loginname=mylogin isolationlevel=read committed (2) xactid=44717634252 currentdb=21 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=adhoc line=1 stmtstart=88 sqlhandle=0x020000005295ed22e6c53906dc31b4433d8c16c8ebb4122c
UPDATE PRINT_QUEUE SET PRQ_STAGE = @stringUP0 WHERE PRQ_XDOC = @string1 AND PRQ_STAGE = '10'
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
inputbuf
(@string1 varchar(10),@stringUP0 varchar(2))UPDATE PRINT_QUEUE SET PRQ_STAGE = @stringUP0 WHERE PRQ_XDOC = @string1 AND PRQ_STAGE = '10'
resource-list
ridlock fileid=1 pageid=1294464 dbid=21 objectname=mylivedb2.dbo.PRINT_QUEUE id=lock1ea796e00 mode=X associatedObjectId=72057594051231744
owner-list
owner id=process6c3288 mode=X
waiter-list
waiter id=process15b2502c8 mode=S requestType=wait
keylock hobtid=72057594093568000 dbid=21 objectname=mylivedb2.dbo.PRINT_QUEUE indexname=IDX1_PRINT_QUEUE id=lock1458eca00 mode=U associatedObjectId=72057594093568000
owner-list
owner id=process15b2502c8 mode=S
waiter-list
waiter id=process6c3288 mode=X requestType=convert

Brian Donahue

I'm happy to see you have got all of the deadlock information.

Is the issue still simply that SQL Monitor can't put the deadlock information into an alert or that you need help troubleshooting the deadlock?

I can raise an issue if SQL Monitor if it's not correctly displaying deadlock information.

Luis Martin

I can see the same information, but there is nothing in mail detail.

AndyF

Brian, the issue is that the summary information is missing from the Details section of the alert; this is useful to see at a glance the processes and objects involved in the deadlock. So you then need to go into the Output section to scroll through the raw trace. This is happening most of the time in my case; as I explained in my earlier post, I see no difference between the deadlock traces for which Details is missing and those for which we did get information in the Details section. I think this needs to be raised as an issue with your developers.

Brian Donahue

It looks like a bug we are trying to fix at the moment that's something to do with multi-line information coming back from SQL Server.

You can still see everything in the raw output.

chriskelly

We think that we have fixed the issues mentioned in this post. Please can you install this patch version of the tool and test it. If you have any problems then please email me at support@red-gate.com and quote this forum post.

ftp://support.red-gate.com//patches/SQL_Monitor/Sql Monitor Web_4.0.1.1816.zip

AndyF

I've installed the patch version. Will report back findings in due course.

Thankyou.

AndyF

Have updated to version 4.0.1.1816 but still the deadlock details are missing in most cases. We have just one deadlock that occurred this morning for which the details were present:

deadlock-list
deadlock victim=process142ed0988
process-list
process id=process142ed0988 taskpriority=0 logused=5268 waitresource=RID: 9:1:980350:2 waittime=2083 ownerId=46832449667 transactionname=UPDATE lasttranstarted=2014-02-05T21:44:28.790 XDES=0xbc2cf970 lockMode=U schedulerid=4 kpid=3352 status=suspended spid=87 sbid=3 ecid=0 priority=0 trancount=2 lastbatchstarted=2014-02-05T21:44:28.790 lastbatchcompleted=2014-02-05T21:44:28.790 clientapp=.Net SqlClient Data Provider hostname=myhostname hostpid=5356 loginname=mylogin isolationlevel=read committed (2) xactid=46832449667 currentdb=9 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=adhoc line=1 stmtstart=178 sqlhandle=0x0200000086436d34ed00441d06ba7f7a0ac8e973c8b56462
UPDATE SHIPMENT_HEADER SET SHH_CARRIER = @stringUP0,SHH_LANE = @stringUP1,SHH_DATESHIPMENT = @datetimeUP2 WHERE SHH_LOAD = @string1 AND SHH_STAGE < '90'
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
inputbuf
(@string1 varchar(20),@stringUP0 varchar(9),@stringUP1 varchar(12),@datetimeUP2 datetime)UPDATE SHIPMENT_HEADER SET SHH_CARRIER = @stringUP0,SHH_LANE = @stringUP1,SHH_DATESHIPMENT = @datetimeUP2 WHERE SHH_LOAD = @string1 AND SHH_STAGE < '90'
process id=process1a8e08 taskpriority=0 logused=10436 waitresource=RID: 9:1:980350:3 waittime=2083 ownerId=46832449666 transactionname=UPDATE lasttranstarted=2014-02-05T21:44:28.790 XDES=0xd2ae43b0 lockMode=U schedulerid=2 kpid=3168 status=suspended spid=92 sbid=3 ecid=0 priority=0 trancount=2 lastbatchstarted=2014-02-05T21:44:28.790 lastbatchcompleted=2014-02-05T21:44:28.787 clientapp=.Net SqlClient Data Provider hostname=myhostname hostpid=5356 loginname=mylogin isolationlevel=read committed (2) xactid=46832449666 currentdb=9 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=adhoc line=1 stmtstart=114 sqlhandle=0x020000000ad76909cd03983a173edeee23ed1c4c6f28b0ec
UPDATE SHIPMENT_HEADER SET SHH_TASKSNEW = @intUP0,SHH_TASKSCURRENT = @intUP1,SHH_TASKSACTIONED = @intUP2 WHERE SHH_CONSIGNMENT = @string1
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
inputbuf
(@string1 varchar(5),@intUP0 int,@intUP1 int,@intUP2 int)UPDATE SHIPMENT_HEADER SET SHH_TASKSNEW = @intUP0,SHH_TASKSCURRENT = @intUP1,SHH_TASKSACTIONED = @intUP2 WHERE SHH_CONSIGNMENT = @string1
resource-list
ridlock fileid=1 pageid=980350 dbid=9 objectname=mylivedb.dbo.SHIPMENT_HEADER id=lock22f804600 mode=X associatedObjectId=72057594059882496
owner-list
owner id=process1a8e08 mode=X
waiter-list
waiter id=process142ed0988 mode=U requestType=wait
ridlock fileid=1 pageid=980350 dbid=9 objectname=mylivedb.dbo.SHIPMENT_HEADER id=lock1722d6300 mode=X associatedObjectId=72057594059882496
owner-list
owner id=process142ed0988 mode=X
waiter-list
waiter id=process1a8e08 mode=U requestType=wait

chriskelly

Would it be possible for you to send me the deadlock graph as it appears in the SQL log? for technical reason (which to be honest I don't fully understand) these are stripped out in SQL Monitor. The developer that I have been working with thinks that this might contains some relevant information.

Please can you send this fully indented SQL Log file version to support@red-gate.com and include a reference to this forum post.

Colin Millerchip

To add to Chris' comment, it's important to retain the indentation in the output. You should be able to see this indentation in the content of the Output tab for the deadlock alert, and this is what we're after.

Thanks,


Colin.

AndyF

Chris, I've sent the deadlock output from our SQL log to your address as requested.


Regards,
Andy

xexex

I also get this problem even I update both web and base to 4.0.1.1816.

chriskelly

I'm sorry for not posting sooner. We believe that we have found a fix to the problem and produced a Private Build containing it. This should be in the next release, but please email support@red-gate.com quoting this forum post for a download link.

xexex, I have logged a support ticket for you have emailed you the download link. Please reply to that email if you have any problems.