DECLARE @spid INT DECLARE @blk INT DECLARE @count INT DECLARE @index INT DECLARE @lock TINYINT SET @lock = 0 CREATE TABLE #temp_who_lock ( id INT IDENTITY(1, 1), spid INT, blk INT ) --if @@error<>0 return @@error INSERT INTO #temp_who_lock ( spid, blk ) SELECT 0, blocked FROM ( SELECT * FROM MASTER..sysprocesses WHERE blocked > 0 )a WHERE NOT EXISTS( SELECT * FROM MASTER..sysprocesses WHERE a.blocked = spid AND blocked > 0 ) UNION SELECT spid, blocked FROM MASTER..sysprocesses WHERE blocked > 0 --if @@error<>0 return @@error SELECT @count = COUNT(*), @index = 1FROM #temp_who_lock --select @count,@index --if @@error<>0 return @@error IF @count = 0BEGIN SELECT '没有阻塞和死锁信息' --return 0END WHILE @index <= @countBEGIN IF EXISTS( SELECT 1 FROM #temp_who_lock a WHERE id > @index AND EXISTS( SELECT 1 FROM #temp_who_lock WHERE id <= @index AND a.blk = spid ) ) BEGIN SET @lock = 1 SELECT @spid = spid, @blk = blk FROM #temp_who_lock WHERE id = @index SELECT '引起数据库死锁的是: ' + CAST(@spid AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' ; SELECT @spid, @blk DBCC INPUTBUFFER(@spid) DBCC INPUTBUFFER(@blk) END SET @index = @index + 1END IF @lock = 0BEGIN SET @index = 1 WHILE @index <= @count BEGIN SELECT @spid = spid, @blk = blk FROM #temp_who_lock WHERE id = @index IF @spid = 0 SELECT '引起阻塞的是:' + CAST(@blk AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' ELSE SELECT '进程号SPID:' + CAST(@spid AS VARCHAR(10)) + '被' + '进程号SPID:' + CAST(@blk AS VARCHAR(10)) + '阻塞,其当前进程执行的SQL语法如下' PRINT (LTRIM(@spid) + '' + LTRIM(@blk)); IF (@spid <> 0) BEGIN DBCC INPUTBUFFER(@spid) -- END DBCC INPUTBUFFER(@blk) --引起阻塞语句 SET @index = @index + 1 END END DROP TABLE #temp_who_lock --return 0 --KILL 64
SQLServer检查是否有死锁的脚本
DECLARE @spid INT
DECLARE @blk INT
DECLARE @count INT
DECLARE @index INT
DECLARE @lock TINYINT
SET @lock =&...
- 发表于 2017-09-15 16:02
- 阅读 ( 639 )
- 分类:数据库
你可能感兴趣的文章
- 使用 IIS 新建WebService站点供Android访问远程sqlserver数据库 102 浏览
- B树、B-树、B+树、B*树都是什么 171 浏览
- SQL中的事务ACID 123 浏览
- 关于MySQL,Oracle和SQLServer的特点以及之间区别 242 浏览
- Mysql语句中当前时间不能直接使用C#中的Date.Now传输 253 浏览
随机文章
- 微信商户基本账户余额不足,请充值后重新发起 7945 浏览
- Java版本的跨域解决方案 824 浏览
- java.lang.IllegalStateException: Mapped class was not specified 3494 浏览
- springboot 带入口参数、spring.profiles.active启动项目 2980 浏览
- CentOS编译安装redis,及主从配置,集群配置 2298 浏览
相关问题
条评论
请先 登录 后评论
发送私信
如果觉得我的文章对您有用,请随意打赏。你的支持将鼓励我继续创作!