Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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: Monday, June 13, 2016 10:13 PM
Points: 1,050, Visits: 1,925
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:49 AM
Points: 5,275, Visits: 10,093
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:49 AM
Points: 5,275, Visits: 10,093
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