Deadlock details not available from some servers

SQL Server performance monitoring and alerting

Moderators: Colin Millerchip, priyasinha, Adam, chriskelly

Deadlock details not available from some servers

Postby rmcneish » Wed Jan 15, 2014 8:51 pm

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.
rmcneish
 
Posts: 2
Joined: Wed Jan 15, 2014 7:58 pm

Postby Brian Donahue » Fri Jan 17, 2014 10:42 am

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
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby Brian Donahue » Tue Jan 21, 2014 9:27 am

This is the sort of thing I'm thinking about: Deadlock related to parallelism.
http://www.sqlservercentral.com/Forums/ ... 391-1.aspx
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby AndyF » Wed Jan 22, 2014 5:01 pm

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
AndyF
 
Posts: 5
Joined: Wed Jan 22, 2014 4:52 pm

Postby Brian Donahue » Mon Jan 27, 2014 4:01 pm

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.
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby Luis Martin » Mon Jan 27, 2014 4:27 pm

I can see the same information, but there is nothing in mail detail.
Luis Martin
 
Posts: 43
Joined: Tue Nov 15, 2005 3:45 pm

Postby AndyF » Wed Jan 29, 2014 11:05 am

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.
AndyF
 
Posts: 5
Joined: Wed Jan 22, 2014 4:52 pm

Postby Brian Donahue » Wed Jan 29, 2014 12:38 pm

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.
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby chriskelly » Wed Jan 29, 2014 3:21 pm

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
Chris Kelly
Technical Support Engineer
chriskelly
 
Posts: 330
Joined: Mon Apr 19, 2010 1:44 pm
Location: Cambridge, UK

Postby AndyF » Fri Jan 31, 2014 4:21 pm

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

Thankyou.
AndyF
 
Posts: 5
Joined: Wed Jan 22, 2014 4:52 pm

Postby AndyF » Thu Feb 06, 2014 10:45 am

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
AndyF
 
Posts: 5
Joined: Wed Jan 22, 2014 4:52 pm

Postby chriskelly » Thu Feb 06, 2014 8:29 pm

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.
Chris Kelly
Technical Support Engineer
chriskelly
 
Posts: 330
Joined: Mon Apr 19, 2010 1:44 pm
Location: Cambridge, UK

Postby Colin Millerchip » Fri Feb 07, 2014 10:27 am

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.
Colin Millerchip
 
Posts: 66
Joined: Wed Oct 31, 2007 5:31 pm
Location: Cambridge, UK

Postby AndyF » Wed Feb 12, 2014 11:27 am

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


Regards,
Andy
AndyF
 
Posts: 5
Joined: Wed Jan 22, 2014 4:52 pm

Postby xexex » Fri Mar 07, 2014 5:04 am

I also get this problem even I update both web and base to 4.0.1.1816.
xexex
 
Posts: 41
Joined: Thu Jun 24, 2010 5:50 am

Next

Return to SQL Monitor 4

Who is online

Users browsing this forum: No registered users and 0 guests

cron