INF: sp_lock2 Returns Additional Locking Details

This article was previously published under Q255596
This article has been archived. It is offered "as is" and will no longer be updated.
Summary
The sp_lock2 procedure is an enhanced version of the sp_lock SQL Server system stored procedure (see SQL Server 7.0 Books Online for more documentation). In addition to the output of sp_lock, sp_lock2 returns the names for users, databases, and tables involved in the current locks, and therefore it can help you analyze a locking scenario.
More information
The sp_lock2 procedure returns the following additional information to sp_lock:
  • User names for the listed system process IDs (SPIDs).
  • Database names for the listed databases.
  • Table names for the listed database objects.
You can use the following script to create the sp_lock2 procedure for SQL Server 7.0:
USE masterGOcreate procedure sp_lock2 @spid1 int = NULL,      /* server process id to check for locks */ @spid2 int = NULL       /* other process id to check for locks */ asset nocount on/*** Show the locks for both parameters.*/ declare @objid int,   @dbid int,   @string Nvarchar(255)CREATE TABLE #locktable   (   spid       smallint   ,loginname nvarchar(20)   ,hostname  nvarchar(30)   ,dbid      int   ,dbname    nvarchar(20)   ,objId     int   ,ObjName   nvarchar(128)   ,IndId     int   ,Type      nvarchar(4)   ,Resource  nvarchar(16)   ,Mode      nvarchar(8)   ,Status    nvarchar(5)   )   if @spid1 is not NULLbegin   INSERT #locktable      (      spid      ,loginname      ,hostname      ,dbid      ,dbname      ,objId      ,ObjName      ,IndId      ,Type      ,Resource      ,Mode      ,Status      )   select convert (smallint, l.req_spid)       --,coalesce(substring (user_name(req_spid), 1, 20),'')      ,coalesce(substring (s.loginame, 1, 20),'')      ,coalesce(substring (s.hostname, 1, 30),'')      ,l.rsc_dbid      ,substring (db_name(l.rsc_dbid), 1, 20)      ,l.rsc_objid      ,''      ,l.rsc_indid      ,substring (v.name, 1, 4)      ,substring (l.rsc_text, 1, 16)      ,substring (u.name, 1, 8)      ,substring (x.name, 1, 5)   from master.dbo.syslockinfo l,      master.dbo.spt_values v,      master.dbo.spt_values x,      master.dbo.spt_values u,      master.dbo.sysprocesses s   where l.rsc_type = v.number   and   v.type = 'LR'   and   l.req_status = x.number   and   x.type = 'LS'   and   l.req_mode + 1 = u.number   and   u.type = 'L'   and   req_spid in (@spid1, @spid2)   and   req_spid = s.spidend/*** No parameters, so show all the locks.*/ elsebegin   INSERT #locktable      (      spid      ,loginname      ,hostname      ,dbid      ,dbname      ,objId      ,ObjName      ,IndId      ,Type      ,Resource      ,Mode      ,Status      )   select convert (smallint, l.req_spid)       --,coalesce(substring (user_name(req_spid), 1, 20),'')      ,coalesce(substring (s.loginame, 1, 20),'')      ,coalesce(substring (s.hostname, 1, 30),'')      ,l.rsc_dbid      ,substring (db_name(l.rsc_dbid), 1, 20)      ,l.rsc_objid      ,''      ,l.rsc_indid      ,substring (v.name, 1, 4)      ,substring (l.rsc_text, 1, 16)      ,substring (u.name, 1, 8)      ,substring (x.name, 1, 5)   from master.dbo.syslockinfo l,      master.dbo.spt_values v,      master.dbo.spt_values x,      master.dbo.spt_values u,      master.dbo.sysprocesses s   where l.rsc_type = v.number   and   v.type = 'LR'   and   l.req_status = x.number   and   x.type = 'LS'   and   l.req_mode + 1 = u.number   and   u.type = 'L'   and   req_spid = s.spid   order by spIDENDDECLARE lock_cursor CURSORFOR SELECT dbid, ObjId FROM #locktable WHERE Type ='TAB'OPEN lock_cursorFETCH NEXT FROM lock_cursor INTO @dbid, @ObjId WHILE @@FETCH_STATUS = 0   BEGIN   SELECT @string =       'USE ' + db_name(@dbid) + char(13)        + 'UPDATE #locktable SET ObjName =  object_name('       + convert(varchar(32),@objId) + ') WHERE dbid = ' + convert(varchar(32),@dbId)       + ' AND objid = ' + convert(varchar(32),@objId)       EXECUTE (@string)    FETCH NEXT FROM lock_cursor INTO @dbid, @ObjId       ENDCLOSE lock_cursorDEALLOCATE lock_cursor SELECT * FROM #locktablereturn (0) -- END sp_lock2GO				
Properties

Article ID: 255596 - Last Review: 12/05/2015 18:53:46 - Revision: 5.0

Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 7.0 Standard Edition

  • kbnosurvey kbarchive kbcodesnippet kbinfo KB255596
Feedback