그린세상님의 이글루

 database프로그램을 하다 보면 잘 작동하던 것이 갑자기 먹통이 되는 경우가 있다.
먹통이 된 이유를 찾다보면 해당 프로세스가 database처리 부분에서 다른 프로세스에 의해
blocking이 되어 꼼짝하지 못하고 있는 경우가 상당한데, 이 때 database에서 blocking하고
있는 프로세스를 찾기 위해서, sp_who2를 사용하여 프로세스를 확인하고, dbcc inputbuffer를
사용하여 해당 프로세스가 수행하고 있는 명령을 확인한다. Blocking하고 있는 프로세스가
하나의 명령을 아주 오래 수행하고 있는 경우라면 위의 작업(sp_who2->blockin하는 process id확인
->dbcc inputbuffer실행)을 하는 동안 기다려 주어 확인이 가능하겠지만, 짧게 blocking했다가
풀리고 하는 경우 확인이 여간 성가신게 아니다. Blocking하는 프로세스의 평균 작업시간이 1초
라고 가정하면, 관리자가 확인하는 도중 해당 session은 다른 작업을 수행해 잘못된 판단을
할 수 있는 개연성도 크다. 그래서 한 번에 이를 수행하는 procedure를 간단히 만들어 보았다.

[그림]수행시 결과 화면

----------------------------------------------------------------------------------------
use master
go

-----------------------------------------------------------------
-- Procedure Name  : sp_blocked
-- Description     : get the buffer of blocked and blocking processes.
-- Inner SP        : NONE
-- Copyleft to everyone
-- Author          : dialup71@gmail.com, 2008-08-28
-- Modify History  : none
-----------------------------------------------------------------
CREATE PROCEDURE [dbo].[sp_blocked]
AS
--declare variables.
DECLARE @v_intBlockedSPID       SMALLINT        --process id of blocked
DECLARE @v_intBlockingSPID      SMALLINT        --process id of blocking
DECLARE @v_hdBlocked            BINARY(20)      --handle of blocked
DECLARE @v_hdBlocking           BINARY(20)      --handle of blocking
DECLARE @v_strBlockedHostname   NVARCHAR(255)   --hostname of blocked
DECLARE @v_strBlockingHostname  NVARCHAR(255)   --hostname of blocking
DECLARE @v_strBlockedDBname     NVARCHAR(255)   --database name of blocked
DECLARE @v_strBlockingDBname    NVARCHAR(255)   --database name of blocking
DECLARE @v_strBlockedProgname   NVARCHAR(255)   --program name of blocked
DECLARE @v_strBlockingProgname  NVARCHAR(255)   --program name of blocking
DECLARE @v_strBlockedBuffer     NVARCHAR(4000)  --buffer of blocked
DECLARE @v_strBlockingBuffer    NVARCHAR(4000)  --buffer of blocking

--create table to store the results.
CREATE TABLE #BLOCKEDINFO(
    BlockedSPID SMALLINT NOT NULL,
    BlockingSPID SMALLINT NOT NULL,
    BlockedHostname NVARCHAR(255),
    BlockingHostname NVARCHAR(255),
    BlockedDBname NVARCHAR(255),
    BlockingDBname NVARCHAR(255),
    BlockedProgname NVARCHAR(255),
    BlockingProgname NVARCHAR(255),
    BlockedBuffer NVARCHAR(4000),
    BlockingBuffer NVARCHAR(4000))
IF @@ERROR <> 0 BEGIN
    PRINT 'failed to create temporary table. quit'

    RETURN
END

--create a cursor of blocked processes information.
DECLARE BLOCKED_CUR CURSOR FOR
SELECT SPID
      ,SQL_HANDLE
      ,HOSTNAME
      ,DB_NAME(dbid)
      ,PROGRAM_NAME
      ,BLOCKED
FROM   MASTER.DBO.SYSPROCESSES WITH(NOLOCK)
WHERE  ISNULL(BLOCKED, 0) <> 0

OPEN BLOCKED_CUR
IF @@ERROR <> 0 BEGIN
    DROP TABLE #BLOCKEDINFO
   
    PRINT 'failed to open cursor. quit1'
    RETURN
END

FETCH NEXT FROM BLOCKED_CUR INTO @v_intBlockedSPID, @v_hdBlocked, @v_strBlockedHostname, @v_strBlockedDBname, @v_strBlockedProgname,
                                 @v_intBlockingSPID
IF @@ERROR <> 0 OR @@FETCH_STATUS <> 0 BEGIN
    DROP TABLE #BLOCKEDINFO

    CLOSE BLOCKED_CUR
    DEALLOCATE BLOCKED_CUR
   
    PRINT 'no processes were blocked at this time.'
    RETURN
END

WHILE ( @@FETCH_STATUS = 0 ) BEGIN
    SELECT @v_hdBlocking = SQL_HANDLE
          ,@v_strBlockingHostname = HOSTNAME
          ,@v_strBlockingDBname = DB_NAME(dbid)
          ,@v_strBlockingProgname = PROGRAM_NAME
    FROM   MASTER.DBO.SYSPROCESSES WITH(NOLOCK)
    WHERE  SPID = @v_intBlockingSPID
    IF @@ROWCOUNT <> 1 BEGIN
        GOTO LABEL_FETCH_NEXT --blocking status was aleady gone away.
    END
   
    SELECT @v_strBlockedBuffer = CAST(TEXT AS NVARCHAR(4000))
    FROM   SYS.DM_EXEC_SQL_TEXT(@v_hdBlocked)
    IF @@ROWCOUNT <> 1 BEGIN
        GOTO LABEL_FETCH_NEXT --blocking status was aleady gone away.
    END
   
    SELECT @v_strBlockingBuffer = CAST(TEXT AS NVARCHAR(4000))
    FROM   SYS.DM_EXEC_SQL_TEXT(@v_hdBlocking)
    IF @@ROWCOUNT <> 1 BEGIN
        GOTO LABEL_FETCH_NEXT --blocking status was aleady gone away.
    END

    INSERT INTO #BLOCKEDINFO(BlockedSPID, BlockingSPID, BlockedHostname, BlockingHostname, BlockedDBname,
                             BlockingDBname, BlockedProgname, BlockingProgname, BlockedBuffer, BlockingBuffer)
                      VALUES(@v_intBlockedSPID, @v_intBlockingSPID, @v_strBlockedHostname, @v_strBlockingHostname, @v_strBlockedDBname,
                             @v_strBlockingDBname, @v_strBlockedProgname, @v_strBlockingProgname, @v_strBlockedBuffer, @v_strBlockingBuffer)
    IF @@ERROR <> 0 BEGIN
        DROP TABLE #BLOCKEDINFO
   
        CLOSE BLOCKED_CUR
        DEALLOCATE BLOCKED_CUR
       
        PRINT 'failed to save buffer to a temporary table. quit'
        RETURN
    END
   
    LABEL_FETCH_NEXT:
    FETCH NEXT FROM BLOCKED_CUR INTO @v_intBlockedSPID, @v_hdBlocked, @v_strBlockedHostname, @v_strBlockedDBname, @v_strBlockedProgname,
                                     @v_intBlockingSPID
END --end of while

CLOSE BLOCKED_CUR
DEALLOCATE BLOCKED_CUR

SELECT BlockedSPID
      ,BlockedHostname
      ,BlockedDBname
      ,BlockedProgname
      ,BlockedBuffer
      ,BlockingSPID
      ,BlockingHostname
      ,BlockingDBname
      ,BlockingProgname
      ,BlockingBuffer
FROM   #BLOCKEDINFO

DROP TABLE #BLOCKEDINFO

RETURN
----------------------------------------------------------------------------------------

이 글과 관련있는 글을 자동검색한 결과입니다 [?]

• block & non-block by 유꼬