ALTER PROC AP_KILL_SESSION
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF OBJECT_ID('TEMPDB..#LOCK') IS NOT NULL
DROP TABLE #LOCK

CREATE TABLE #LOCK
(IDX INT IDENTITY(1,1)
,TRANSACTION_ISOLATION_LEVEL INT
,SESSION_ID INT)

DECLARE @MAX INT, @SESSION_ID INT, @SQL VARCHAR(8000), @SESSION_DESC VARCHAR(8000)

INSERT INTO #LOCK (TRANSACTION_ISOLATION_LEVEL, SESSION_ID)
SELECT TRANSACTION_ISOLATION_LEVEL ,SESSION_ID
  FROM SYS.DM_EXEC_SESSIONS
 WHERE SESSION_ID > 50
   AND TRANSACTION_ISOLATION_LEVEL IN (3,4)

SELECT @MAX = MAX(IDX) FROM #LOCK
WHILE (@MAX > 0) BEGIN
 SELECT @SESSION_ID = SESSION_ID FROM #LOCK WHERE IDX = @MAX

-- SELECT @SQL

    SELECT @SESSION_DESC = 'KILLED SESSION_ID : ' + CAST(@SESSION_ID AS VARCHAR(100))
   + ', HOST_NAME : ' + ISNULL(HOST_NAME,'NULL')
   + ', PROGRAM_NAME : ' + ISNULL(PROGRAM_NAME,'NULL')
   + ', LOCAL_NET_ADDRESS : ' + ISNULL(LOCAL_NET_ADDRESS,'NULL')
   + ', HOST_PROCESS_ID : ' + ISNULL(CAST(HOST_PROCESS_ID AS VARCHAR(1000)),'NULL')
   FROM SYS.DM_EXEC_SESSIONS A
   JOIN SYS.DM_EXEC_CONNECTIONS B
  ON A.SESSION_ID = B.SESSION_ID
  WHERE A.SESSION_ID = @SESSION_ID
    AND A.SESSION_ID > 50

 SET @SQL = CAST(@SESSION_DESC AS VARCHAR(8000))+' TRANSACTION ISOLATION LEVEL SERIALIZABLE'
  RAISERROR (@SQL, 15,  1) WITH LOG 
 SET @SQL = 'KILL '+CAST(@SESSION_ID AS VARCHAR(100))
 EXEC (@SQL)

 SET @MAX = @MAX - 1
END

이 게시물을...




profile

select top 1 * from world where hobby = 'sql' and sqlguru = 1 order by sqllevel desc;

go
김민석  minsouk@hotmail.com 010-9967-0955 http://cafe.naver.com/sqlmvp