Differences

This shows you the differences between two versions of the page.

Link to this comparison view

tsql:locks [2019/08/02 16:58]
dyson created
tsql:locks [2019/08/02 17:11] (current)
dyson [Which objects are locked by a blocking session]
Line 5: Line 5:
  
 <code tsql>​--/​* <code tsql>​--/​*
 +DROP TABLE IF EXISTS #locks
 +
 CREATE TABLE #locks ( CREATE TABLE #locks (
   [spid] SMALLINT   [spid] SMALLINT
Line 26: Line 28:
 , [Status] , [Status]
 ) )
-EXEC sp_lock+--EXEC sp_lock 
 +SELECT 
 +  request_session_id 
 +, resource_database_id 
 +, IIF(resource_type = '​OBJECT',​ resource_associated_entity_id,​ 0) 
 +, NULL  -- IndId 
 +, NULL  -- [Type] 
 +, resource_description 
 +, request_mode 
 +, request_status 
 +FROM sys.dm_tran_locks 
 --*/ --*/
 SELECT ​ SELECT ​
Line 32: Line 45:
 , OBJECT_SCHEMA_NAME([ObjId],​ [dbid]) , OBJECT_SCHEMA_NAME([ObjId],​ [dbid])
 , OBJECT_NAME([ObjId],​ [dbid]) , OBJECT_NAME([ObjId],​ [dbid])
-COUNT(*)+, *
 FROM #locks FROM #locks
-WHERE [spid] IN ( +WHERE [ObjId] > 0 
-  SELECT blocking_session_id +--AND [spid] IN ( 
-  FROM sys.dm_os_waiting_tasks +--  ​SELECT blocking_session_id 
-  WHERE blocking_session_id <> session_id +--  FROM sys.dm_os_waiting_tasks 
-+--  WHERE blocking_session_id <> session_id 
-AND [ObjId] > 0 +--
-GROUP BY    +--GROUP BY    
-  DB_NAME([dbid]) +--  ​DB_NAME([dbid]) 
-, OBJECT_SCHEMA_NAME([ObjId],​ [dbid]) +--, OBJECT_SCHEMA_NAME([ObjId],​ [dbid]) 
-, OBJECT_NAME([ObjId],​ [dbid])+--, OBJECT_NAME([ObjId],​ [dbid])
 </​code>​ </​code>​