实时输出SQL Server的lock信息

有时候在调试SQL时候，需要实时查看lock信息，执行以下脚本可实现此功能。

use master

go

while 1 =1

begin

print 'Start time: ' + convert(varchar(26), getdate(), 121)

Print 'Running processes'

select spid, blocked, waittype, waittime, lastwaittype, waitresource, dbid, uid, cpu, physical_io, memusage, login_time, last_batch,

open_tran, status, hostname, program_name, cmd, net_library, loginame

from sysprocesses

--where (kpid <> 0 ) or (spid < 51)

-- Change it if you only want to see the working processes

print '*********lockinfor***********'

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, 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'

order by spid

print 'inputbuffer for running processes'

declare @spid varchar(6)

declare ibuffer cursor fast_forward for

select cast (spid as varchar(6)) as spid from sysprocesses where spid >50

open ibuffer

fetch next from ibuffer into @spid

while (@@fetch_status != -1)

begin

print ''

print 'DBCC INPUTBUFFER FOR SPID ' + @spid

exec ('dbcc inputbuffer (' + @spid + ')')

fetch next from ibuffer into @spid

end

deallocate ibuffer

waitfor delay '0:0:10'

end