检查锁定SQL Server数据库的Process ID代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】检查锁定SQL Server数据库的Process ID

作者:DTC2 / 发布于2015/8/10/ 916


	CREATE PROCEDURE #sp_who_lock
	AS
	BEGIN
	 DECLARE @spid INT
	 DECLARE @blk INT
	 DECLARE @count INT
	 DECLARE @index INT
	 DECLARE @lock TINYINT
	
	 SET @lock = 0
	
	 DECLARE @temp_who_lock AS TABLE (
	 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 TOP 1 1
	 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(1),
	 @index = 1
	 FROM @temp_who_lock
	
	 IF @@error <> 0
	 RETURN @@error
	
	 IF @count = 0
	 BEGIN
	 SELECT N'没有阻塞和死锁信息'
	
	 RETURN 0
	 END
	
	 WHILE @index <= @count
	 BEGIN
	 IF EXISTS (
	 SELECT TOP 1 1
	 FROM @temp_who_lock a
	 WHERE id > @index
	 AND EXISTS (
	 SELECT TOP 1 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 N'引起数据库死锁的是:' + CAST(@spid AS NVARCHAR(10)) + N'进程号,其执行的SQL语法如下'
	
	 SELECT @spid,
	 @blk
	
	 DBCC INPUTBUFFER (@spid)
	
	 DBCC INPUTBUFFER (@blk)
	 END
	
	 SET @index = @index + 1
	 END
	
	 IF @lock = 0
	 BEGIN
	 SET @index = 1
	
	 WHILE @index <= @count
	 BEGIN
	 SELECT @spid = spid,
	 @blk = blk
	 FROM @temp_who_lock
	 WHERE id = @index
	
	 IF @spid = 0
	 SELECT N'引起阻塞的是:' + CAST(@blk AS NVARCHAR(10)) + N'进程号,其执行的SQL语法如下'
	 ELSE
	 SELECT N'进程号SPID：' + CAST(@spid AS NVARCHAR(10)) + N'被进程号SPID：' + CAST(@blk AS NVARCHAR(10)) + N'阻塞,其当前进程执行的SQL语法如下'
	
	 DBCC INPUTBUFFER (@spid)
	
	 DBCC INPUTBUFFER (@blk)
	
	 SET @index = @index + 1
	 END
	 END
	
	 RETURN 0
	END
	GO
	
	EXEC #sp_who_lock

试试其它关键字

　Process　　ID　

同语言下

可能有用的

DTC2贡献的其它代码(12)