MSSQL LOCK 정보 보기 (DBA 관리용 시스템 프로시저)

MSSQL의 LOCK 정보를 보는 프로시저이다.

/************************************************************************************************

-- sp_lock0

-- 락 정보를 보여준다.

-- 첫번째 파라미터는 server process id to check for locks

-- 두번째 파라미터는 other process id to check for locks

************************************************************************************************/

USE master

GO

IF OBJECT_ID('sp_lock0') IS NOT NULL

   DROP PROCEDURE sp_lock0

GO

CREATE PROC sp_lock0

@spid1 INT = NULL,

@spid2 INT = NULL 

AS

  SET NOCOUNT ON

  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  DECLARE @objid INT,

     @indid INT,

     @dbid INT,

     @string VARCHAR(255)

  CREATE TABLE #locktable

  ( spid      SMALLINT

  , loginname VARCHAR(20)

  , hostname  VARCHAR(30)

  , dbid      INT

  , dbname    SYSNAME

  , ObjOwner  VARCHAR(128)

  , objId     INT

  , ObjName   VARCHAR(128)

  , IndId     INT

  , IndName   VARCHAR(128)

  , Type      VARCHAR(4)

  , Resource  VARCHAR(16)

  , Mode      VARCHAR(8)

  , Status    VARCHAR(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

  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 cur_lock2 CURSOR

  FOR SELECT dbid, ObjId, IndId 

      FROM #locktable

      WHERE Type <>'DB' AND Type <> 'FIL'

  

  OPEN cur_lock2

  

  FETCH NEXT FROM cur_lock2 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)

  

    EXEC (@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)

  

    EXEC (@string)

  

    FETCH NEXT FROM cur_lock2 INTO @dbid, @ObjId, @IndId

  END

  

  CLOSE cur_lock2

  DEALLOCATE cur_lock2

  

  SELECT * FROM #locktable

  DROP TABLE #locktable

  

  RETURN(0) --sp_lock0

GO

  

GRANT EXECUTE ON sp_lock0 TO PUBLIC

GO