출처 : http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005Admin&intSeq=137

[실행중인 프로세스 확인하기] - 아래의 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;

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

동적 관리 뷰 활용 - 현재 실행중인 프로세스 쿼리 확인하기, 현재 동작중 프로세스 죽이기