잠겨있는(locking) 프로세스를 진단해주는 SQL 스크립트

PS. SQL Server 2005 Database 에서도 테스트 하였습니다.
쿼리결과를 "텍스트로 결과 표시"로 하셔서 보시기 바랍니다.

[출처 : http://cafe.naver.com/devcore/70]

어떤 프로세스에 락(lock)이 걸려있는지, 그 프로세스에 대한 정보 및 어떤 테이블이 블록킹
당했는지 등을 보여주는 스크립트이다. 이 스크립트는 SQL 서버 7.0과 2000에서 테스트되었다.
이 SQL 스크립트는 다음과 같다.

 
Print 'Server and datetime of report'
select @@servername, getdate()
 
Print 'This will tell you which processes are causing the database blocking on the server.'
Print ''
Print 'If you need to kill any processes, use these spid''s. Make sure you save the report before killing any processes.'
Print ''
set nocount on
SELECT * FROM master..sysprocesses where spid IN (select blocked from master..sysprocesses) and blocked = 0
 
Print 'Below this is a snapshot of all the SQL processes on the server.'
Print 'Save the report and send to the whole database group.'
Print ''
SELECT * FROM master..sysprocesses
 
Print 'These are the queries are causing the blocks.'
Print ''
 
DECLARE @spid int
DECLARE SPID_CURSOR CURSOR FOR 
SELECT spid
FROM master..sysprocesses 
WHERE spid IN ( select blocked 
                from master..sysprocesses) 
AND blocked = 0
FOR READ ONLY
 
OPEN SPID_CURSOR
 
FETCH SPID_CURSOR INTO @spid
 
WHILE @@fetch_status <> -1
BEGIN
        IF @@fetch_status <> -2
        BEGIN
        SELECT '@spid = ' + CONVERT(varchar(20),@spid)
        DBCC INPUTBUFFER (@spid)
        END
        FETCH SPID_CURSOR INTO @spid
END
CLOSE SPID_CURSOR
 
DEALLOCATE SPID_CURSOR
Print 'These are the processes that are being blocked:'
Print ''
SELECT spid FROM master..sysprocesses where blocked <> 0
 
SELECT @spid = NULL
 
Print 'This is the lock info for the blocking processes.'
Print ''
 
        select  convert (smallint, req_spid) As spid, 
                rsc_dbid As dbid, 
                rsc_objid As ObjId,
                
                rsc_indid As IndId,
                substring (v.name, 1, 4) As Type,
                substring (rsc_text, 1, 16) as Resource,
                substring (u.name, 1, 8) As Mode,
                substring (x.name, 1, 5) As Status
        into #BlockerLockInfo
        
        from    master.dbo.syslockinfo,
                master.dbo.spt_values v,
                master.dbo.spt_values x,
                master.dbo.spt_values u
        where   master.dbo.syslockinfo.rsc_type = v.number
                        and v.type = 'LR'
                        and master.dbo.syslockinfo.req_status = x.number
                        and x.type = 'LS'
                        and master.dbo.syslockinfo.req_mode + 1 = u.number
                        and u.type = 'L'
                        and req_spid in (select spid from master..sysprocesses where blocked = 0 and spid IN (select blocked from master..sysprocesses))
 
DECLARE @dbid int,
        @sqlstring nvarchar(255)
 
DECLARE DBIDcursor CURSOR FOR 
SELECT DISTINCT dbid 
FROM #BlockerLockInfo
order by dbid
--SELECT *
--FROM #BlockerLockInfo
 
OPEN DBIDcursor
 
FETCH DBIDcursor INTO @dbid
 
WHILE @@fetch_status <> -1
BEGIN
        IF @@fetch_status <> -2
        BEGIN
        
        SELECT @sqlstring = N'SELECT #BlockerLockInfo.*, a.name FROM #BlockerLockInfo , '+name+'..sysobjects a  WHERE dbid =@dbid AND #BlockerLockInfo.ObjID = a.id ORDER BY spid' from master..sysdatabases where dbid = @dbid
        --SELECT @sqlstring     
        EXEC sp_executesql @sqlstring, N'@dbid int', @dbid
        END
        FETCH DBIDcursor INTO @dbid
 
END
CLOSE DBIDcursor
DEALLOCATE DBIDcursor
drop table #BlockerLockInfo

[출처 : http://korea.internet.com - 테크]