Locking Information - Simple SQL

,

Here's a simple SQL to give you locking information.
The SQL tells you what resource type you are using, the object name, lock request mode, lock status, and also the calling program's name.
And all this from a simple query on sysprocesses, and syslockinfo!

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

Rate

Share

Share

Rate