常用脚本--查看死锁和阻塞usp_who_lock
2024-08-30 21:19:03
USE [master]
GO /****** Object: StoredProcedure [dbo].[sp_who_lock] Script Date: 02/07/2014 11:51:24 ******/
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO CREATE PROCEDURE [dbo].[usp_who_lock]
AS
BEGIN
DECLARE @spid INT,@bl INT,
@intTransactionCountOnEntry INT,
@intRowcount INT,
@intCountProperties INT,
@intCounter INT CREATE TABLE #tmp_lock_who (
id INT IDENTITY(1,1),
SPID SMALLINT,
bl SMALLINT) IF @@ERROR<>0 RETURN @@ERROR INSERT INTO #tmp_lock_who(SPID,bl) SELECT 0 ,blocked
FROM (SELECT * FROM sysprocesses WHERE blocked>0 ) a
WHERE NOT EXISTS(SELECT * FROM (SELECT * FROM sysprocesses WHERE blocked>0 ) b
WHERE a.blocked=SPID)
UNION SELECT SPID,blocked FROM sysprocesses WHERE blocked>0 IF @@ERROR<>0 RETURN @@ERROR -- 找到临时表的记录数
SELECT @intCountProperties = COUNT(*),@intCounter = 1
FROM #tmp_lock_who IF @@ERROR<>0 RETURN @@ERROR IF @intCountProperties=0
SELECT '现在没有阻塞和死锁信息' AS MESSAGE -- 循环开始
WHILE @intCounter <= @intCountProperties
BEGIN
-- 取第一条记录
SELECT @spid = SPID,@bl = bl
FROM #tmp_lock_who WHERE Id = @intCounter
BEGIN
IF @spid =0
SELECT '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
ELSE
SELECT '进程号SPID:'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID:'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
IF @spid>0
DBCC INPUTBUFFER (@spid )
END -- 循环指针下移
SET @intCounter = @intCounter + 1
END DROP TABLE #tmp_lock_who RETURN 0
END
GO --===============================
--Usage
EXEC [master].[dbo].[usp_who_lock]
最新文章
- 初学DDD-领域驱动设计
- 如何用MediaCapture解决二维码扫描问题
- 获取设备的mac地址可靠的方法
- Robots Exclusion Protocol简介
- 浅谈Apache Spark的6个发光点(CSDN)
- MSSQL数据库逻辑文件名修改与查看
- eclipse 中使用等宽字体 inconsolata
- tomcat+mysql数据库连接池的操作
- [每日一题] 11gOCP 1z0-053 :2013-09-30 ASMCMD.......................................................8
- Docker远程访问get(root)shell姿势
- jquery表单序列化
- WPF制作带明细的环形图标
- JS禁用浏览器退格键、禁止右键、禁止全选、复制、粘贴
- HardNet解读
- JDBC 和连接池
- zookeeper学习day01
- Elasticsearch 2.4 安装
- git必备命令
- 使用concurrent.futures模块并发,实现进程池、线程池
- Repository与Factory关系