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

Need XQuery help Expand / Collapse
Author
Message
Posted Tuesday, January 28, 2014 4:11 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, August 21, 2014 2:00 PM
Points: 627, Visits: 4,978
Hi all, I am trying to get the values for spid and inputbuf under the blocked-process node as well as the spid and inputbuf under the blocking-process node. Can you please help me with the xquery? Thanks in advance.

<event name="blocked_process_report" package="sqlserver" timestamp="2014-01-28T22:13:03.814Z">
<data name="duration">
<value>18655000</value>
</data>
<data name="database_id">
<value>2</value>
</data>
<data name="object_id">
<value>2209392</value>
</data>
<data name="index_id">
<value>20480</value>
</data>
<data name="lock_mode">
<value>3</value>
<text>S</text>
</data>
<data name="transaction_id">
<value>124085777</value>
</data>
<data name="resource_owner_type">
<value>0x00000001</value>
<text>LOCK</text>
</data>
<data name="blocked_process">
<value>
<blocked-process-report monitorLoop="221377">
<blocked-process>
<process id="process1f4c17498" taskpriority="0" logused="0" waitresource="KEY: 2:5764607667828948992 (61a06abd401c)" waittime="18655" ownerId="124085777" transactionname="SELECT" lasttranstarted="2014-01-28T14:12:45.157" XDES="0x2c3a8cd00" lockMode="S" schedulerid="1" kpid="5716" status="suspended" spid="87" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2014-01-28T14:12:45.157" lastbatchcompleted="2014-01-28T14:12:45.150" lastattention="1900-01-01T00:00:00.150" clientapp="Microsoft SQL Server Management Studio - Query" hostname="ABC490" hostpid="14568" loginname="mydomain\username" isolationlevel="read committed (2)" xactid="124085777" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame line="1" sqlhandle="0x02000000d9de7b2f4f3a78e40f100bc02a84efbb9f01a84d0000000000000000000000000000000000000000" />
</executionStack>
<inputbuf>
SELECT * FROM t1 </inputbuf>
</process>
</blocked-process>
<blocking-process>
<process status="suspended" waittime="20966" spid="92" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2014-01-28T14:12:42.847" lastbatchcompleted="2014-01-28T14:12:42.833" lastattention="1900-01-01T00:00:00.833" clientapp="Microsoft SQL Server Management Studio - Query" hostname="ABC490" hostpid="14568" loginname="domain\username" isolationlevel="read committed (2)" xactid="124085736" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame line="3" stmtstart="100" stmtend="150" sqlhandle="0x020000005a74b3030117e049389a93b2ce5bfb48e272f9380000000000000000000000000000000000000000" />
</executionStack>
<inputbuf>
BEGIN TRANSACTION
INSERT INTO t1 DEFAULT VALUES
WAITFOR DELAY '00:00:30'
COMMIT </inputbuf>
</process>
</blocking-process>
</blocked-process-report>
</value>
</data>
<data name="database_name">
<value>tempdb</value>
</data>
</event>




Post #1535655
Posted Tuesday, January 28, 2014 5:54 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:42 PM
Points: 1,787, Visits: 5,696
declare @x xml = '<event name="blocked_process_report" package="sqlserver" timestamp="2014-01-28T22:13:03.814Z">
<data name="duration">
<value>18655000</value>
</data>
<data name="database_id">
<value>2</value>
</data>
<data name="object_id">
<value>2209392</value>
</data>
<data name="index_id">
<value>20480</value>
</data>
<data name="lock_mode">
<value>3</value>
<text>S</text>
</data>
<data name="transaction_id">
<value>124085777</value>
</data>
<data name="resource_owner_type">
<value>0x00000001</value>
<text>LOCK</text>
</data>
<data name="blocked_process">
<value>
<blocked-process-report monitorLoop="221377">
<blocked-process>
<process id="process1f4c17498" taskpriority="0" logused="0" waitresource="KEY: 2:5764607667828948992 (61a06abd401c)" waittime="18655" ownerId="124085777" transactionname="SELECT" lasttranstarted="2014-01-28T14:12:45.157" XDES="0x2c3a8cd00" lockMode="S" schedulerid="1" kpid="5716" status="suspended" spid="87" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2014-01-28T14:12:45.157" lastbatchcompleted="2014-01-28T14:12:45.150" lastattention="1900-01-01T00:00:00.150" clientapp="Microsoft SQL Server Management Studio - Query" hostname="ABC490" hostpid="14568" loginname="mydomain\username" isolationlevel="read committed (2)" xactid="124085777" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame line="1" sqlhandle="0x02000000d9de7b2f4f3a78e40f100bc02a84efbb9f01a84d0000000000000000000000000000000000000000" />
</executionStack>
<inputbuf>
SELECT * FROM t1 </inputbuf>
</process>
</blocked-process>
<blocking-process>
<process status="suspended" waittime="20966" spid="92" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2014-01-28T14:12:42.847" lastbatchcompleted="2014-01-28T14:12:42.833" lastattention="1900-01-01T00:00:00.833" clientapp="Microsoft SQL Server Management Studio - Query" hostname="ABC490" hostpid="14568" loginname="domain\username" isolationlevel="read committed (2)" xactid="124085736" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame line="3" stmtstart="100" stmtend="150" sqlhandle="0x020000005a74b3030117e049389a93b2ce5bfb48e272f9380000000000000000000000000000000000000000" />
</executionStack>
<inputbuf>
BEGIN TRANSACTION
INSERT INTO t1 DEFAULT VALUES
WAITFOR DELAY ''00:00:30''
COMMIT </inputbuf>
</process>
</blocking-process>
</blocked-process-report>
</value>
</data>
<data name="database_name">
<value>tempdb</value>
</data>
</event>'

select
@x.value('(//blocked-process/process/@spid)[1]','integer') as blocked_spid
,@x.value('(//blocked-process/process/inputbuf/text())[1]','varchar(max)') as blocked_inputbuf
,@x.value('(//blocking-process/process/@spid)[1]','integer') as blocking_spid
,@x.value('(//blocking-process/process/inputbuf/text())[1]','varchar(max)') as blocking_inputbuf



MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1535674
    Posted Wednesday, January 29, 2014 1:37 PM
    SSChasing Mays

    SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

    Group: General Forum Members
    Last Login: Thursday, August 21, 2014 2:00 PM
    Points: 627, Visits: 4,978
    Thank you Mister!


    Post #1536098
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse