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 - 테크]
