[MSSQL] DB LOCK 걸린 SQL 알아보기

DB Lock관련해서 sp_lock부분을 조금 이용하여 아래 LOCK걸린구문 찾는 쿼리를 한번 만들어 보았다.

TOP 1 부분이 좀 애석하긴 한데 그래도 이정도가 어디야~~~

-------------

declare @spid int = 0

select top 1 @spid = spid --, dbid, ObjId, IndId, Type, Resource, Mode, Status 

from (

select convert (smallint, req_spid) As spid,

rsc_dbid As dbid,

rsc_objid As ObjId,

rsc_indid As IndId,

substring (v.name, 1, 4) As Type,

substring (rsc_text, 1, 32) 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') a where a.Mode = 'X'

group by spid --, dbid, ObjId, IndId, Type, Resource, Mode, Status

PRINT '@spid > ' + CONVERT(VARCHAR(5), @spid)

if @SPID > 0

begin

   DBCC INPUTBUFFER(@spid)

end

---------------------

@spid > 0

혹은

Language Event | 0 | exec up_tRecvOrdSys 'RBC', '',1

요로케 나올것이다.