You are currently offline, waiting for your internet to reconnect

INF: sp_lock2 Returns Additional Locking Details

This article was previously published under Q255596
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: 03/20/2013 19:07:00 - Revision: 5.0

  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • kbcodesnippet kbinfo KB255596
Feedback