I am trying to create custom SAM component for blocking session using SQL query option but I am unable to get the output.
Below is the query that I am using here for component creation.
SELECT
db_name(t1.resource_database_id) as [Database] -- DATABASE NAME WHERE BLOCKING IS BEING OBSERVED
,t1.resource_associated_entity_id as [BlkObject] -- ID OF THE RESOURCE WHERE LOCK IS BEING HELD
,t1.resource_type -- TYPE OF RESOURCE (DB/key/page etc..)
,t1.request_mode -- LOCK REQUEST MODE (SHARED/EXCLUSIVE/INTENET EXCLUSIVE etc..)
,t1.request_status -- STATUS OF LOCK REQUEST (GRANT/WAIT etc..)
,t1.request_session_id -- SPID of waiter
,ISNULL((select text from sys.dm_exec_requests as r -- GET SQL FOR WAITER
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) where r.session_id = t1.request_session_id), '') as waiter_text
,t2.blocking_session_id -- SPID OF BLOCKER
,ISNULL((select text from master.sys.sysprocesses as p -- GET SQL FOR BLOCKER
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) where p.spid = t2.blocking_session_id), '') as blocker_text
,SJS.SPID as JobSPID -- SPID of JOB WHICH IS GETTING BLOCKED OR IS BLOCKING
,SJS.AppName -- GET APPLICATION NAME
,SJS.JobName -- GET JOB NAME
FROM sys.dm_tran_locks AS t1
INNER JOIN sys.dm_os_waiting_tasks as t2 on t1.lock_owner_address = t2.resource_address
INNER JOIN sys.dm_exec_requests AS WAITERREQ ON t1.request_session_id = WAITERREQ.session_id
INNER JOIN sys.dm_exec_requests AS BLOCKERREQ ON t2.session_id = BLOCKERREQ.session_id
LEFT JOIN (
select X.session_id as SPID, X.program_name as AppName, Y.name as JobName
from sys.dm_exec_sessions as X with(NOLOCK)
inner join msdb.dbo.sysjobs as Y with(NOLOCK)
on X.program_name like (N'SQLAgent - TSQL JobStep (Job ' + CONVERT(VARCHAR(36), CONVERT(BINARY(16), Y.job_id), 1) + '%')
and X.program_name like 'SQLAgent - TSQL JobStep (Job 0x%'
and Y.enabled = 1
) AS SJS ON SJS.SPID = t1.request_session_id
I need to create a report on blocking history, Can anyone help on that to create a report for blocking history using above query.
