Script of the day – Locking Information


This one is an oldie but a goodie. I don’t remember who originally gave me this, but I’ve edited it over the years. Works on SQL Server 2000 and 2005.


/*


usc_DBA_Show_Lock_Types.sql


Author: Unknown


Purpose: Shows the various locks taken on a system


*/


SELECT LTRIM( rtrim( hostname ) ) HostName,


spid ProcesId,


Program_Name,


rsc_dbid DBID,


DB_NAME( rsc_dbid ) DBNAME,


CASE rsc_type


WHEN 1 THEN ‘No resource used’


WHEN 2 THEN ‘Database’


WHEN 3 THEN ‘File’


WHEN 4 THEN ‘Index’


WHEN 5 THEN ‘Table’


WHEN 6 THEN ‘Page’


WHEN 7 THEN ‘Key’


WHEN 8 THEN ‘Extent’


WHEN 9 THEN ‘RID (Row ID)’


WHEN 10 THEN ‘Application’


END ResourceType,


rsc_objid OBJID,


OBJECT_NAME( rsc_objid ) OBJNAME,


CASE req_mode


WHEN 0 THEN ‘No access to recource’


WHEN 1 THEN ‘Sch-S: Schema Stability’


WHEN 2 THEN ‘Sch-M: Schema Modification’


WHEN 3 THEN ‘S: Shared’


WHEN 4 THEN ‘U: Update’


WHEN 5 THEN ‘X: Exclusive’


WHEN 6 THEN ‘IS: Intent Shared’


WHEN 7 THEN ‘IU: Intent Update’


WHEN 8 THEN ‘IX: Intent Exclusive’


WHEN 9 THEN ‘SIU: Shared Intent Update’


WHEN 10 THEN ‘SIX: Shared Intent Exclusive’


WHEN 11 THEN ‘UIX: Update Intent Exclusive’


WHEN 12 THEN ‘BU: Used by bulk operations’


WHEN 13 THEN ‘RangeS_S: Shared Key-Range and Shared Resource lock’


WHEN 14 THEN ‘RangeS_U: Shared Key-Range and Update Resource lock’


WHEN 15 THEN ‘RangeI_N: Insert Key-Range and Null Resource lock’


WHEN 16 THEN ‘RangeI_S: Key-Range Conversion lock – overlap of RangeI_N and S locks’


WHEN 17 THEN ‘RangeI_U: Key-Range Conversion lock – overlap of RangeI_N and U locks’


WHEN 18 THEN ‘RangeI_X: Key-Range Conversion lock – overlap of RangeI_N and X locks’


WHEN 19 THEN ‘RangeX_S: Key-Range Conversion lock – overlap of RangeI_N and RangeS_S locks’


WHEN 20 THEN ‘RangeX_U: Key-Range Conversion lock – overlap of RangeI_N and RangeS_U locks’


WHEN 21 THEN ‘RangeX_X: Exclusive Key-Range and Exclusive Resource lock (WHEN updating a key in a range)’


END RequestMode,


CASE req_status


WHEN 1 THEN ‘Granted’


WHEN 2 THEN ‘Converting’


WHEN 3 THEN ‘Waiting’


END ReqStatus,


CASE req_ownertype


WHEN 1 THEN ‘Transaction’


WHEN 2 THEN ‘Cursor’


WHEN 3 THEN ‘Session’


WHEN 4 THEN ‘ExSession’


END ReqOwner


FROM master..sysprocesses, master..syslockinfo


WHERE spid = req_spid


ORDER BY 1, 2, 3


GO