Capturing Blocking info

• PearlJammer1

  SSCrazy Eights

  Points: 8483

  More actions

  April 16, 2013 at 4:43 am

  #273604

  I am trying to capture blocking info with the teqnique detailed below. The issue i am having is that if i run the TSQL Listed at the bottom of this post to capture the blocking info then it works ok when i manually run from ssms but when i try to automate it, it runs (i cheked the job history) but i dont see any output ?

  I have created a table as detailed below:

  CREATE TABLE dbo.t1

  (c1 INT

  , (c2 INT

  , (c3 DATETIME);

  INSERT INTO dbo.t1

  VALUES (11, 12, GETDATE());

  INSERT INTO dbo.t1

  VALUES (21, 22 GETDATE());

  I then create a blocking situation using the 2 queries below:

  BEGIN TRAN User1

  UPDATE dbo.t1

  SET c3 = GETDATE();

  Then i run query 2:

  BEGIN TRAN User2

  SELECT c2 FROM dbo.t1 WHERE c1 = 11;

  COMMIT

  The alert condition i have created fires a job to capture the blocking info:

  The TSQL is listed below:

  SELECT tl.request_session_id AS WaitingSessionID

  ,wt.blocking_session_id AS BlockingSessionID

  ,wt.resource_description

  ,wt.wait_type

  ,wt.wait_duration_ms

  ,DB_NAME(tl.resource_database_id) AS DatabaseName

  ,tl.resource_associated_entity_id AS WaitingAssociatedEntity

  ,tl.resource_type AS WaitingResourceType

  ,tl.request_type AS WaitingRequestType

  ,wrt.[text] AS WaitingTSql

  ,btl.request_type BlockingRequestType

  ,brt.[text] AS BlockingTsql

  FROM sys.dm_tran_locks tl

  JOIN sys.dm_os_waiting_tasks wt

  ON tl.lock_owner_address = wt.resource_address

  JOIN sys.dm_exec_requests wr

  ON wr.session_id = tl.request_session_id

  CROSS APPLY sys.dm_exec_sql_text(wr.sql_handle) AS wrt

  LEFT JOIN sys.dm_exec_requests br

  ON br.session_id = wt.blocking_session_id

  OUTER APPLY sys.dm_exec_sql_text(br.sql_handle) AS brt

  LEFT JOIN sys.dm_tran_locks AS btl

  ON br.session_id = btl.request_session_id

  why does this work ok when i manually run it (ie i get to see the blocked process info) but doesnt work when i run it via an alert/sql job ( as i said it does run it is just i cant see any output)

  This is probably something really basic so i hope somebody can help.

  Thanks

  Steven

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  April 16, 2013 at 4:51 am

  #1606764

  Steven

  You're just doing the SELECT but not doing anything with the results. I think you can set up your job to divert the output to a text file or such like, but my preferred solution would be to create a table with the same columns as the output as your query, and then write your query like this:

  INSERT INTO MyTable

  SELECT...

  John