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

Capturing Blocking info Expand / Collapse
Author
Message
Posted Tuesday, April 16, 2013 4:43 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 18, 2014 4:49 AM
Points: 242, Visits: 961
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
Post #1442677
Posted Tuesday, April 16, 2013 4:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:48 AM
Points: 5,367, Visits: 9,915
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
Post #1442681
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse