Query block process report

• wogderess

  SSC Rookie

  Points: 32

  More actions

  August 2, 2014 at 11:15 am

  #289397

  Comments posted to this topic are about the item Query block process report

• aleksey donskoy

  Mr or Mrs. 500

  Points: 565

  More actions

  August 5, 2014 at 8:11 am

  #1735209

  Completed with the trace file pathame finder:

  IF OBJECT_ID('tempdb..#block_process_reports') is not null

  drop table #block_process_reports

  declare @TraceFile varchar(100)

  --set @TraceFile = 'InsertTraceFileHere'

  -- Listing 1. Finding trace file location using sys.traces

  select @TraceFile = REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),LEN(path)))+'log.trc'

  FROM sys.traces

  WHERE path LIKE '%\MSSQL\Log\log%.trc';

  select TextData

  ,StartTime

  ,EndTime

  ,LoginName

  into #block_process_reports

  from fn_trace_gettable(@TraceFile, default)

  where TextData like '<block%'

  Alter table #block_process_reports alter column TextData XML

  SELECT

  TextData.value('(/blocked-process-report/blocked-process/process/@clientapp)[1]', 'nvarchar(max)') AS 'Blocked Client App'

  ,TextData.value('(/blocked-process-report/blocking-process/process/@clientapp)[1]', 'nvarchar(max)') AS 'Blocking Client App'

  ,TextData.value('(/blocked-process-report/blocked-process/process/inputbuf)[1]', 'nvarchar(max)') AS 'Blocked Process'

  ,TextData.value('(/blocked-process-report/blocking-process/process/inputbuf)[1]', 'nvarchar(max)') AS 'Blocking Process'

  ,TextData.value('(/blocked-process-report/blocked-process/process/@trancount)[1]', 'nvarchar(max)') AS 'Tran count blocked'

  ,TextData.value('(/blocked-process-report/blocking-process/process/@trancount)[1]', 'nvarchar(max)') AS 'Tran count blocking'

  ,TextData.value('(/blocked-process-report/blocked-process/process/@hostname)[1]', 'nvarchar(max)') AS 'Blocked hostname'

  ,TextData.value('(/blocked-process-report/blocking-process/process/@hostname)[1]', 'nvarchar(max)') AS 'BlockING hostname'

  ,TextData

  ,StartTime

  ,LoginName

  FROM

  #block_process_reports

  --WHERE TextData.value('(/blocked-process-report/blocking-process/process/inputbuf)[1]', 'nvarchar(max)') LIKE '%statement text%'

  where

  (TextData.value('(/blocked-process-report/blocked-process/process/@clientapp)[1]', 'nvarchar(max)') like 'appname'

  or TextData.value('(/blocked-process-report/blocking-process/process/@clientapp)[1]', 'nvarchar(max)') like 'appname')

  order by StartTime