Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Lock View: sp_lock2

By Alexander Chigrik,


Introduction

As a SQL Server DBA, I often need a reference to information about locks. Microsoft recommends to use sp_lock system stored procedure to report locks information. This very useful procedure returns the information about SQL Server process ID, which lock the data, about locked database ID, about locked object ID, about locked index ID and about type of locking (type, resource, mode and status columns).

This is the results set of sp_lock stored procedure:

spid   dbid   ObjId       IndId  Type Resource         Mode     Status
------ ------ ----------- ------ ---- ---------------- -------- ------
1      1      0           0      DB                    S        GRANT
6      1      0           0      DB                    S        GRANT
7      1      0           0      DB                    S        GRANT
8      1      0           0      DB                    S        GRANT
9      1      0           0      DB                    S        GRANT
9      2      0           0      DB                    S        GRANT
9      1      117575457   0      TAB                   IS       GRANT
10     1      0           0      DB                    S        GRANT
11     5      0           0      DB                    S        GRANT

The information, returned by sp_lock stored procedure, is needed in some clarification, because it's difficult to understand database name, object name and index name by their ID numbers.

Microsoft provides an enhanced version of the sp_lock system stored procedure, which returns user name, host name, database name and object name also.

You can find this stored procedure at here:

INF: sp_lock2 Returns Additional Locking Details http://support.microsoft.com/support/kb/articles/Q255/5/96.ASP

This enhanced stored procedure works under SQL Server 7.0 as well, but has syntax error under SQL Server 2000. It does not return the name of an index also.

sp_lock2

Here you can find the new version of the sp_lock2 stored procedure for SQL Server 7.0 and SQL Server 2000. This version returns index name and object owner also.

You can use the following script to create the sp_lock2 procedure:

USE MASTER
GO
create procedure sp_lock2
@spid1 int = NULL,      /* server process id to check for locks */
@spid2 int = NULL       /* other process id to check for locks */
as

set nocount on
/*
** Show the locks for both parameters.
*/
declare @objid int,
   @indid int,
   @dbid int,
   @string Nvarchar(255)

CREATE TABLE #locktable
   (
   spid       smallint
   ,loginname nvarchar(20)
   ,hostname  nvarchar(30)
   ,dbid      int
   ,dbname    nvarchar(20)
   ,ObjOwner  nvarchar(128)
   ,objId     int
   ,ObjName   nvarchar(128)
   ,IndId     int
   ,IndName   nvarchar(128)
   ,Type      nvarchar(4)
   ,Resource  nvarchar(16)
   ,Mode      nvarchar(8)
   ,Status    nvarchar(5)
   )

if @spid1 is not NULL
begin
   INSERT #locktable
      (
      spid
      ,loginname
      ,hostname
      ,dbid
      ,dbname
      ,ObjOwner
      ,objId
      ,ObjName
      ,IndId
      ,IndName
      ,Type
      ,Resource
      ,Mode
      ,Status
      )
   select convert (smallint, l.req_spid)
      ,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.spid
end
/*
** No parameters, so show all the locks.
*/
else
begin
   INSERT #locktable
      (
      spid
      ,loginname
      ,hostname
      ,dbid
      ,dbname
      ,ObjOwner
      ,objId
      ,ObjName
      ,IndId
      ,IndName
      ,Type
      ,Resource
      ,Mode
      ,Status
      )
   select convert (smallint, l.req_spid)
      ,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 spID
END
DECLARE lock_cursor CURSOR
FOR SELECT dbid, ObjId, IndId FROM #locktable
  WHERE Type <>'DB' and Type <> 'FIL'

OPEN lock_cursor
FETCH NEXT FROM lock_cursor INTO @dbid, @ObjId, @IndId
WHILE @@FETCH_STATUS = 0
   BEGIN

   SELECT @string =
      'USE ' + db_name(@dbid) + char(13)
      + 'update #locktable set ObjName = name, ObjOwner = USER_NAME(uid)'
      + ' from sysobjects where id = ' + convert(varchar(32),@objid)
      + ' and ObjId = ' + convert(varchar(32),@objid)
      + ' and dbid = ' + convert(varchar(32),@dbId)

   EXECUTE (@string)

   SELECT @string =
      'USE ' + db_name(@dbid) + char(13)
      + 'update #locktable set IndName = i.name from sysindexes i '
      + ' where i.id = ' + convert(varchar(32),@objid)
      + ' and i.indid = ' + convert(varchar(32),@indid)
      + ' and ObjId = ' + convert(varchar(32),@objid)
      + ' and dbid = ' + convert(varchar(32),@dbId)
      + ' and #locktable.indid = ' + convert(varchar(32),@indid)

   EXECUTE (@string)

   FETCH NEXT FROM lock_cursor INTO @dbid, @ObjId, @IndId
   END
CLOSE lock_cursor
DEALLOCATE lock_cursor

SELECT * FROM #locktable
return (0)
-- END sp_lock2
GO

Literature

  1. SQL Server Books Online
  2. sp_lock (T-SQL) http://support.microsoft.com/support/SQL/Content/inprodhlp/_sp_lock.asp
  3. INF: sp_lock2 Returns Additional Locking Details http://support.microsoft.com/support/kb/articles/Q255/5/96.ASP
Total article views: 7376 | Views in the last 30 days: 8
 
Related Articles
FORUM

object name from objid

object name from objid

FORUM

SUBSTRING Question?

SUBSTRING

FORUM
FORUM

substring

using substring to parse data from string

FORUM

DBCC SHOWCONTIG and indid

DBCC SHOWCONTIG and indid

Tags
performance tuning    
sql server 6.5    
sql server 7    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones