It is very
common to deal with blocking scenario and there may be a blocking chain means
one SPID is blocking second one and second one is blocking third one and so on
so it's really difficult to track the root of blocking chain by simply running
sp_who2 or from activity monitor if there are number of SPIDs
which are involved so here I wish to share some T-SQL which will help you to
find out the root SPID and all associated details with that SPID in order to
enable one to further troubleshoot and make prompt decision:
1) To start , one can
simply run the below T-SQL but it will only be helpful If there are few
connections or only one SPID is blocking another otherwise it will be really
difficult to check all SPID involved manually :
select blocked,*
from sysprocesses where
blocked<>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 :
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 login which is output of first query
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 :
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 login which is output of first query
Hope this
will help you..Thanks/
Brgds,
Chhavinath Mishra
