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


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:08 PM
Points: 1,000, Visits: 1,687
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, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
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, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
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