[MSSQL] 락(lock) 걸린 쿼리(Query)를 확인하고 해제하기

출처 : http://yongandju.tistory.com/74

데이타베이스(DB)를 운영하다 보면 가끔 이유 없이 조회 결과가 나오지 않거나, 

타임아웃(TimeOut)에 걸려 커넥션(Connection)이 끊어지는 경우가 종종 있습니다.

메모리나 CPU가 비정상적으로 높은 수치를 유지하고 있어서 DBMS가 정상 작동하지 않는다거나

하는 경우의 증상이 그러하며 왕왕 테이블(Table) 락(Lock)이 발생한게 그 이유가 되기도 합니다ㅜ

운영 중인 DBMS가 MS-SQL일 경우 간단하게 락(Lock)을 검출하고 이를 해제하는 방법을 소개합니다.

-- Lock 걸린 SPID 검출 (Mode 부분이 X 표시된것이 LOCK)

EXEC sp_lock 

--Lock 걸린 spid를 입력하면 현재 돌고 있는 쿼리를 출력해 준다.

dbcc inputbuffer(80)

--Lock을 유발하는 spid를 종료시킨다.

kill 78

-- Lock으로 의심되는 spid만 추출

select p.status,  p.program_name, p.hostname

, p.spid, p.blocked, p.kpid, p.cpu, p.physical_io, p.waittype, p.waittime, p.lastwaittype, p.waitresource, p.dbid

, p.uid, p.memusage, p.login_time, p.last_batch

, p.ecid, p.open_tran, p.sid, p.hostprocess

, p.cmd, p.nt_domain, p.nt_username, p.net_address, p.net_library, p.loginame

, p.context_info, p.sql_handle, p.stmt_start, p.stmt_end

FROM master..sysprocesses p

where (status like 'run%' or waittime > 0 OR blocked <> 0 OR open_tran <> 0

OR EXISTS(SELECT * FROM master..sysprocesses p1 where p.spid = p1.blocked and p1.spid <> p1.blocked)

  )

AND spid > 50

AND spid <> @@spid

ORDER BY CASE WHEN status like 'run%' THEN 0 ELSE 1 END

, waittime DESC, open_tran desc