有时候在调试SQL时候,需要实时查看lock信息,执行以下脚本可实现此功能。
use master
gowhile 1 =1beginprint '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, loginamefrom sysprocesses--where (kpid <> 0 ) or (spid < 51)-- Change it if you only want to see the working processesprint '*********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 Statusfrom master.dbo.syslockinfo,master.dbo.spt_values v,master.dbo.spt_values x,master.dbo.spt_values uwhere master.dbo.syslockinfo.rsc_type = v.numberand v.type = 'LR'and master.dbo.syslockinfo.req_status = x.numberand x.type = 'LS'and master.dbo.syslockinfo.req_mode + 1 = u.numberand u.type = 'L'order by spidprint 'inputbuffer for running processes'declare @spid varchar(6)declare ibuffer cursor fast_forward forselect cast (spid as varchar(6)) as spid from sysprocesses where spid >50open ibufferfetch next from ibuffer into @spidwhile (@@fetch_status != -1)beginprint ''print 'DBCC INPUTBUFFER FOR SPID ' + @spidexec ('dbcc inputbuffer (' + @spid + ')')fetch next from ibuffer into @spidenddeallocate ibufferwaitfor delay '0:0:10'end