1. 락 조회
select a.status, a.program_name, a.hostname, a.spid, a.blocked, a.kpid,
a.cpu, a.physical_io, a.waittype, a.waittime, a.lastwaittype,
a.waitresource, a.dbid, a.uid, a.memusage, a.login_time, a.last_batch,
a.ecid, a.open_tran, a.sid, a.hostprocess,
a.cmd, a.nt_domain, a.nt_username, a.net_address,
a.net_library, a.loginame, a.context_info, a.sql_handle,
a.stmt_start, a.stmt_end
from master..sysprocesses a
where (
status like 'run%'
or waittime > 0
or blocked <> 0
or open_tran <> 0
or exists ( select *
from master..sysprocesses p
where a.spid = p.blocked
and p.spid <> p.blocked
)
)
and spid > 50
and spid <> @@spid
order by
case when status like 'run%'
then 0
else 1
end,
waittime desc,
open_tran desc
2. 락(Lock) 확인
select * from sys.sysprocesses where blocked >0
3. 동작중인 쿼리 확인 조회 - BlkBy 항목에 값이 있으면 Lock
exec sp_who2
4. 강제로 Kill 하기
kill [sp_id]
5. Lock 되어있는 유저 쿼리 조회
dbcc inputbuffer ( sp_id )
'메모' 카테고리의 다른 글
MS Sql 프로시저, 함수, 테이블, 뷰 조회 (0) | 2022.12.15 |
---|
댓글