July 10, 2002 at 2:30 am
Hello,
I was having a look at the locking stored procedures, in particular, sp_lock2 posted in the script library.
I think a simple sql will do a better job :
selectltrim( 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
frommaster.dbo.sysprocesses, master.dbo.syslockinfo
wherespid = req_spid
order by 1, 2, 3
go
What do you all think?
Regards,
Diwakar
--
July 10, 2002 at 5:30 am
Makes lot of sense. Thanks.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply