Capturing Blocking info

  • 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

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply