Deadlock details not available from some servers
rmcneish
Posts: 4
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.
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.
Comments
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?
http://www.sqlservercentral.com/Forums/ ... 391-1.aspx
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
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.
You can still see everything in the raw output.
ftp://support.red-gate.com//patches/SQL_Monitor/Sql Monitor Web_4.0.1.1816.zip
Thankyou.
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
Please can you send this fully indented SQL Log file version to support@red-gate.com and include a reference to this forum post.
Thanks,
Colin.
Regards,
Andy
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.