[MSSQL팁] sp_lock(Transact-SQL) LOCK(잠금) 걸린 실행중인 스크립트 찾고 KILL로 종료 시키기

안녕하세요 철사마 입니다.

DB가 문제없이 잘 돌아가면 좋겠지만 멀쩡하던 쿼리가 갑자기 느려지는 경우가 있습니다.

원인 중 하나가 LOCK이 걸려서 그런 경우가 있는데요, 현재 DB에 LOCK이 걸린 쿼리가 있는지 조회하고 해당 쿼리 실행을 종료하는 것까지 정리해 보았습니다.

 

 

일반적으로 sp_lock 실행을 하려면 실행 권한이 있어야 하는데 실행권한이 없으면 아래와 같은 오류 메시지가 발생합니다.

 

사용자에게 이 동작을 수행할 권한이 없습니다. 

 

이때 자신의 계정에 VIEW SERVER STATE 권한이 필요 합니다.

 

grant alter server state to '권한을 줄 계정이름'

 

권한이 부여가 되어 스크립트를 실행할 준비가 되었다면 아래를 참고해 주세요

 

1. 현재 실행중안 DB의 잠금에 대한 정보를 확인합니다.

-- Lock이 걸린 SPID를 추출합니다.
-- Mode 부분이 X로 나오면 LOCK이 걸린 것입니다.
EXEC sp_lock 108 

-- Mode가 X로 되어 있는 spid의 숫자를 대입합니다.
-- SPID의 소유자가 누구인지 확인합니다.
EXEC sp_who2 108

-- LOCK이 걸린 대상 스크립트가 출력됩니다.
DBCC INPUTBUFFER(108)

-- LOCK을 유발시키는 spid를 종료 시킵니다.
KILL 108 

 

위에 설명이 되었던 sp_lock 에 대해 열 이름을 상세하게 표로 정리된 내용이 공식 microsoft 사이트에 공개되어 있어 정리하였습니다.

(출처 : https://docs.microsoft.com/ko-kr/sql/relational-databases/system-stored-procedures/sp-lock-transact-sql?view=sql-server-ver15 )