Cadavre (9/24/2013)

---

One of these queries may help.

--== Locks held in database ==--

SELECT request_session_id AS [spid], DB_NAME(resource_database_id) AS [dbname],

CASE WHEN resource_type = 'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id)

WHEN resource_associated_entity_id = 0 THEN 'n/a'

ELSE OBJECT_NAME(p.object_id) END AS [entity_name], index_id, resource_type AS [resource],

resource_description AS [description], request_mode AS mode, request_status AS [status]

FROM sys.dm_tran_locks t

LEFT OUTER JOIN sys.partitions p ON p.partition_id = t.resource_associated_entity_id

WHERE t.resource_type <> 'DATABASE';

--== Blocking queries in database ==--

SELECT DES.Session_ID AS [Root Blocking Session ID], DER.STATUS AS [Blocking Session Request Status],

DES.Login_Time AS [Blocking Session Login Time], DES.Login_Name AS [Blocking Session Login Name],

DES.Host_Name AS [Blocking Session Host Name], Coalesce(DER.Start_Time, DES.Last_Request_Start_Time) AS [Request Start Time],

CASE WHEN DES.Last_Request_End_Time >= DES.Last_Request_Start_Time THEN DES.Last_Request_End_Time ELSE NULL END AS [Request End Time],

Substring(TEXT, DER.Statement_Start_Offset / 2, CASE WHEN DER.Statement_End_Offset = - 1 THEN DataLength(TEXT) ELSE DER.Statement_End_Offset / 2 END) AS [Executing Command],

CASE WHEN DER.Session_ID IS NULL THEN 'Blocking session does not have an open request and may be due to an uncommitted transaction.'

WHEN DER.Wait_Type IS NOT NULL THEN 'Blocking session is currently experiencing a ' + DER.Wait_Type + ' wait.'

WHEN DER.STATUS = 'Runnable' THEN 'Blocking session is currently waiting for CPU time.'

WHEN DER.STATUS = 'Suspended' THEN 'Blocking session has been suspended by the scheduler.'

ELSE 'Blocking session is currently in a ' + DER.STATUS + ' status.'

END AS [Blocking Notes]

FROM Sys.DM_Exec_Sessions DES(READUNCOMMITTED)

LEFT JOIN Sys.DM_Exec_Requests DER(READUNCOMMITTED) ON DER.Session_ID = DES.Session_ID

OUTER APPLY Sys.DM_Exec_Sql_Text(DER.Sql_Handle)

WHERE DES.Session_ID IN (SELECT Blocking_Session_ID

FROM Sys.DM_Exec_Requests(READUNCOMMITTED)

WHERE Blocking_Session_ID <> 0 AND Blocking_Session_ID

NOT IN (SELECT session_id

FROM Sys.DM_Exec_Requests(READUNCOMMITTED)

WHERE Blocking_Session_ID <> 0

)

);