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

检查锁定SQLServer数据库的ProcessID 无 CREATE PROCEDURE #sp_who_lockASBEGINDECLARE @spid INTDECLARE @blk INTDECLARE @count INTDECLARE @index INTDECLARE @lock TINYINT ??SET @lock = 0 ??DECLARE @temp_who_lock AS TABLE (id INT identity(1, 1),sp

检查锁定SQL Server数据库的Process ID <style> .CodeEntity .code_pieces ul.piece_anchor{width:25px;position:absolute;top:25px;left:-30px;z-index:1000;} .CodeEntity .code_pieces ul.piece_anchor li{width:25px;background: #efe;margin-bottom:2px;} .CodeEntity .code_pieces ul.piece_anchor li{border-left:3px #40AA63 solid;border-right:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li:hover{border-right:3px #40AA63 solid;border-left:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li a{color: #333;padding: 3px 10px;} .CodeEntity .code_pieces .jump_to_code{visibility:hidden;position:relative;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code{visibility:visible;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code a{text-decoration:none;} .CodeEntity .code_pieces h2 i{float:right;font-style:normal;font-weight:normal;} .CodeEntity .code_pieces h2 i a{font-size:9pt;background: #FFFFFF;color:#00A;padding: 2px 5px;text-decoration:none;} </style> <!—ecms

–> <!—ecms
• $velocityCount

–> <!—ecms

–>

CREATE PROCEDURE #sp_who_lockASBEGIN	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  @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<i style="color:transparent">本文来源gaodai$ma#com搞$$代**码网$</i>)) + N'阻塞,其当前进程执行的SQL语法如下' ??			DBCC INPUTBUFFER (@spid) ??			DBCC INPUTBUFFER (@blk) ??			SET @index = @index + 1		END	END ??	RETURN 0ENDGO?EXEC #sp_who_lock

喜欢 (0)赏

[搞代码]

分享 (0)