This is to identify root blocking and clear blocking pid from JD EDwards
1) Identify Blocking Process
SELECT session_id, wait_duration_ms, wait_type, blocking_session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id <> 0
2) In case there is a blocking chain means one SPID is blocking second one and second one is blocking third one so it's really difficult to track the root of blocking chain if there are number of SPID involved in it so here is the T-SQL which will help to find the root of this blocking chain. Output if this T-SQL will return SPID that is part of blocking chain but it's request_status is 'GRANT'. If the lock is already acquired then it will be in GRANT status otherwise it will be in WAIT status.
SELECT
distinct(l.request_SESSION_Id)
FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND
( l.resource_description = l1.resource_description
OR
(l.resource_description IS NULL
AND l1.resource_description IS NULL))
and (l.request_status)='GRANT' and l.request_SESSION_Id not in (SELECT
distinct(l.request_SESSION_Id)
FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND
( l.resource_description = l1.resource_description
OR
(l.resource_description IS NULL
AND l1.resource_description IS NULL))
and (l.request_status)='WAIT')
order by (l.request_SESSION_Id)
3) T-SQL to get all the details like login , database name and underlying query which is associated with particular SPID :
Note down the SPID and SQL Statement
SELECT
sp.spid, sp.[status], sp.loginame,
sp.hostname, sp.[program_name],
sp.blocked, sp.open_tran,
dbname=db_name(sp.[dbid]), sp.cmd,
sp.waittype, sp.waittime, sp.last_batch, st.[text]
FROM master.dbo.sysprocesses sp
CROSS APPLY sys.dm_exec_sql_text (sp.[sql_handle]) st
WHERE spid = ? -- Please specify the PID from Step 2
4) Get Host_Process_ID and host_name
-- Host_Name is JDEdwards Enterprise Serer
-- Host_Process_ID is Process ID of Kernel
select * from sys.dm_exec_sessions where session_id= ? -- Please specify SPID from step 2
5) Go to Server Manager and Find User connected Kernel
-- Host_Name is JDEdwards Enterprise Serer
-- Host_Process_ID is Process ID of Kernel on Enterprise Server
Look for Call object Kernel Active threads and user. Note down information
Recycle Call object kernel
6) Login to Enterprise Server
Terminate the kernel to clear blocking
7) Review logs to identify root cause of blocking.
