[실행중인 프로세스 확인하기] - 아래의 spid와 본문의 session_id는 같음
SELECT r.session_id
,STATUS
,wait_type
,substring(qt.TEXT, r.statement_start_offset / 2, (
CASE
WHEN r.statement_end_offset = - 1
THEN len(convert(NVARCHAR(max), qt.TEXT)) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset
) / 2) AS query_text --- 현재실행중인일괄처리또는프로시저
,qt.dbid
,qt.objectid
,r.cpu_time
,r.total_elapsed_time
,r.reads
,r.writes
,r.logical_reads
,r.scheduler_id
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt
WHERE r.session_id > 50
ORDER BY r.scheduler_id
,r.STATUS
,r.session_id;
GO
kill [session_id]
출처 : http://blog.naver.com/PostView.nhn?blogId=hyunchul15&logNo=110086384849
[현재 동작중 프로세스 죽이기] - 위의 session_id와 본문의 spid는 같음
SELECT convert(SMALLINT, req_spid) AS spid
,rsc_dbid AS dbid
,db_name(rsc_dbid)
,rsc_objid AS ObjId
,rsc_indid AS IndId
,substring(v.NAME, 1, 4) AS Type
,substring(rsc_text, 1, 16) AS Resource
,substring(u.NAME, 1, 8) AS Mode
,substring(x.NAME, 1, 5) AS STATUS
FROM master.dbo.syslockinfo
,master.dbo.spt_values v
,master.dbo.spt_values x
,master.dbo.spt_values u
WHERE master.dbo.syslockinfo.rsc_type = v.number
AND v.type = 'LR'
AND master.dbo.syslockinfo.req_status = x.number
AND x.type = 'LS'
AND master.dbo.syslockinfo.req_mode + 1 = u.number
AND u.type = 'L'
AND substring(u.NAME, 1, 8) = 'X'
--and rsc_dbid = 27
ORDER BY spid
kill [spid number]
[MSSQL] 락(lock) 걸린 쿼리(Query)를 확인하고 해제하기
출처 : http://yongandju.tistory.com/74
-- Lock 걸린 SPID 검출 (Mode 부분이 X 표시된 것이 LOCK)
exec sp_lock
-- Lock 걸린 SPID를 입력하면 현재 동작중인 쿼리 출력
dbcc inputbuffer(65)
-- 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
