博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
实时输出SQL Server的lock信息
阅读量:5334 次
发布时间:2019-06-15

本文共 1568 字,大约阅读时间需要 5 分钟。

有时候在调试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

转载于:https://www.cnblogs.com/dotagg/p/6364480.html

你可能感兴趣的文章
使用git pull文件时和本地文件冲突怎么办?
查看>>
centos下部署NFS
查看>>
[leedcode 225] Implement Stack using Queues
查看>>
Oracle数据库中的SOUNDEX函数
查看>>
WebService中使用自定义类的解决方法(5种)
查看>>
SQL 存储过程入门(事务)
查看>>
SQLServer如何删除字段中的某个字符串,或者替换为空格?
查看>>
12、xamarin form中实现H5 网页唤醒微信支付的方法
查看>>
spring aop advice注解实现的几种方式
查看>>
oracle数据库查询全系整理
查看>>
用servlet进行用户名和密码校验
查看>>
yii2 实现文章底部"上/下一篇"的功能
查看>>
1014 装箱问题
查看>>
【Core Spring】三、AOP
查看>>
contso7配置静态IP,Linux固定IP地址,主机IP映射
查看>>
memcache命令行
查看>>
Robotics 41013: Lab Assignment
查看>>
WCF初探-4:WCF消息交换模式之请求与答复模式
查看>>
SD-关于定价日期的设置
查看>>
linux小技巧
查看>>