[위로]

<원인>

<SQL문>


CREATE PROC sp_block
AS
set nocount on
IF NOT EXISTS (SELECT * FROM sysprocesses WHERE spid IN
(SELECT blocked FROM sysprocesses )
)
BEGIN
PRINT 'No one is blocked'
RETURN
END

SELECT spid, blocked, status,
loginame,
hostname,
dbname = substring(db_name(dbid), 1, 10)
, cmd
into #tmp
FROM sysprocesses
WHERE blocked <> 0
OR ( spid IN (SELECT blocked FROM sysprocesses))
order by spid

select * from #tmp --차단된 프로세스를 보여준다.

--이제 dbcc inputbuffer를 수행한다.
CREATE TABLE #dbcc (
id int identity
, spid int null
, eventType varchar(255) null
, parameters int null
, eventInfo varchar(255) null
)

DECLARE c1 CURSOR READ_ONLY
FOR SELECT spid FROM #tmp

DECLARE @spid int
OPEN c1

FETCH NEXT FROM c1 INTO @spid
WHILE (@@fetch_status =0)
BEGIN
DECLARE @message varchar(100)
SELECT @message = 'dbcc inputbuffer (' + convert(varchar(30), @spid) + ')'
-- print @message
INSERT #dbcc (eventType, parameters, eventInfo)
exec (@message)
UPDATE #dbcc SET spid = @spid WHERE id = @@identity
FETCH NEXT FROM c1 INTO @spid
END

DEALLOCATE c1

SELECT * FROM #dbcc

<조치 사항>

  1. 차단된 프로세스 Kill (최악의 사항)
  2. 격리 수준 조정 (SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED)
  3. 임시 Table 사용 금지 (테이블 변수 사용)

<기타>

  1. 작업 스케쥴을 통한 정기적인 모니터링 가능 (1분 단위로 수행, Lock 발생시 메일 발송 및 Lock 현황 저장