April 16, 2013 at 4:43 am
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
April 16, 2013 at 4:51 am
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