DB가 Lock 걸리면 실행도 안돼고 문제가 발생함
SELECT GETDATE() AS SYSDATE
, P.STATUS
, P.LAST_BATCH
, P.PROGRAM_NAME
, P.HOSTNAME
, P.SPID
, 'DBCC INPUTBUFFER(' + CONVERT(VARCHAR, P.SPID) + ')' AS LOCK_CHECK_QUERY
, P.BLOCKED
, P.CMD
, P.KPID
, P.CPU
, P.PHYSICAL_IO
, P.WAITTYPE
, P.WAITTIME
, P.LASTWAITTYPE
FROM MASTER..SYSPROCESSES P WITH(NOLOCK)
WHERE (STATUS LIKE 'RUN%'
OR WAITTIME > 0
OR BLOCKED <> 0
OR OPEN_TRAN <> 0
OR EXISTS (
SELECT 'EXISTS'
FROM MASTER..SYSPROCESSES P1 WITH(NOLOCK)
WHERE P.SPID = P1.BLOCKED
AND P1.SPID <> P1.BLOCKED
)
)
AND SPID > 50
AND SPID <> @@SPID
AND P.PROGRAM_NAME NOT LIKE 'SQL SERVER PROFILER%' -- 프로파일러 제외
AND P.LOGINAME NOT LIKE '%SYSTEM%' -- SYSTEM 로그인 제외
ORDER BY LAST_BATCH
DB Lock Check 쿼리를 사용해서 상태가 Sleeping 인 행의 SPID를 찾아서 KILL 시켜버리자
