Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

extracting waitresource from a blocked process report Expand / Collapse
Author
Message
Posted Wednesday, March 27, 2013 7:09 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, August 11, 2014 6:19 PM
Points: 988, Visits: 1,645
Hi,

I've got a "blocked process report" trace and want to get all the waitresoure values for a blocked process:
select top 5 CHARINDEX('waitresource', convert(varchar, TextData)), substring(convert(varchar, TextData), CHARINDEX('waitresource', convert(varchar, TextData)), 20)
FROM [dbo].[Blocking_25032013] (nolock)

I am getting the below results, meaning that the query can't find the 'waitresource' substring in the TextData column:
(No column name) (No column name)
0 <blocked-process-re
0 <blocked-process-re
0 <blocked-process-re
0 <blocked-process-re
0 <blocked-process-re

All I want is to get "OBJECT: 5:142623551:3 " string out of:0
<blocked-process-report>
<blocked-process>
<process id="processae27948" taskpriority="0" logused="0" waitresource="OBJECT: 5:142623551:3 " waittime="5042" ownerId="634429087" transactionname="SELECT" lasttranstarted="2013-03-27T12:36:57.410" XDES="0x167c6ac90" lockMode="IS" schedulerid="4" kpid="3652" status="suspended" spid="250" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2013-03-27T12:36:56.133" lastbatchcompleted="2013-03-27T12:36:56.133" clientapp=".Net SqlClient Data Provider" hostname="RBISYDIISP003" hostpid="16360" loginname="kenticouser" isolationlevel="read committed (2)" xactid="634429087" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>

Any ideas?

Thanks.




Post #1436237
Posted Tuesday, April 9, 2013 11:08 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, August 18, 2014 9:16 PM
Points: 4,576, Visits: 8,347
convert(varchar, TextData)) does not have the length of varchar soecified.
By default it's equal to convert(varchar(30), TextData)).

And actually you do not need to convert Textdata at all.
Use PATINDEX instead of CHARINDEX.
Post #1440647
Posted Tuesday, April 9, 2013 11:15 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, August 18, 2014 9:16 PM
Points: 4,576, Visits: 8,347
select top 5 PATINDEX ('%waitresource=OBJECT%', TextData)
, substring(TextData, PATINDEX ('%waitresource="OBJECT%', TextData) + LEN('waitresource="OBJECT')+1, 20)

Post #1440648
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse