viewing object locks in SQL 2000
It's old stuff but I just thought I might post this code just in case someone else finds it useful:-
I was looking at a process which was blocking its self, as this was a data extract ( only reads ) allegedly there shouldn't have been blocking, the simplest way was to examine the locks which were current, this query shows the locks per object in a nice readable output - if your database is busy then you might want to try filtering by spid as the output can be large. Run it against the database you're interested in as the query is database specific.
--
-- run this against the database to monitor
--
select
convert (int, sli.req_spid) As Spid,
db_name(sli.rsc_dbid) As dbid,
object_name(sli.rsc_objid) As 'Object Name',
sli
.rsc_indid As 'Index Id',
substring (v1.name, 1, 4) As 'Lock Type',
substring (sli.rsc_text, 1, 16) as Resource,
substring (v3.name, 1, 8) As 'Lock Mode',
substring (v2.name, 1, 5) As 'Lock Status'
from
master
.dbo.syslockinfo sli with (nolock) join master.dbo.spt_values v1 on sli.rsc_type = v1.number
join master.dbo.spt_values v2 on sli.req_status = v2.number
join master.dbo.spt_values v3 on sli.req_mode + 1 = v3.number
where
v1
.type = 'LR'
and v2.type = 'LS'
and v3.type = 'L'
and rsc_dbid = db_id() and rsc_objid >0
order
by sli.req_spid
go