Technical Article

System Lock Snapshot


This script captures the current system lock in a temp table, then builds a temp translation table for database and object id by iterating through all of the databases.  It then produces a report by joining the two tables.

For better performance, you could make the translation table perminante and only update it when you are going to be looking at locking.

create table #locks (spid int, dbid int, objid int, indid int, type varchar(25), resource varchar(25),
mode varchar(25), status varchar(25))

create table #objects (dbid int, objid int, dbname varchar(255), objname varchar(255))

exec sp_MSforeachdb 'insert into #objects select db.dbid,,, from ?..sysobjects so, 
master..sysdatabases db
where = ''?''', '?'

exec sp_MSforeachdb 'insert into #objects select db.dbid, 0,, '''' from master..sysdatabases db
where = ''?''', '?'

insert into #locks exec sp_lock

select l.spid, o.dbname, o.objname, l.dbid, l.objid, l.indid, l.type, l.resource, l.mode, l.status
 from #locks l
left outer join #objects o on l.dbid = o.dbid and l.objid = o.objid
where l.objid <> 0
order by spid, o.dbname, o.objname

drop table #locks
drop table #objects