查询数据库中进程死锁情况脚本。

查询数据库中进程死锁情况，脚本。

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_who_lock]    Script Date: 04/26/2012 11:13:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_who_lock]
AS
BEGIN
	DECLARE @spid int,
		@bl int,
		@intTransactionCountOnEntry  int,
        @intRowcount    int,
        @intCountProperties   int,
        @intCounter    int
	
	DECLARE @tmp_lock_who TABLE
	(
		id INT IDENTITY(1,1),
		spid SMALLINT,
		bl SMALLINT
	)
	
	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

	-- 找到临时表的记录数
	SELECT  @intCountProperties = Count(*),@intCounter = 1
	FROM @tmp_lock_who
	
	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 )
		END 

		-- 循环指针下移
		SET @intCounter = @intCounter + 1
	END
END

其它查询正在执行进程信息

WITH tb
AS
(
	SELECT blocking_session_id,session_id,DB_NAME(database_id) as dbname, [text]
	FROM master.Sys.dm_exec_requests a
	CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)
),
tb1 as
(
	SELECT a.*,login_time,program_name,client_interface_name,login_name,cpu_time,memory_usage*8 as 'memory_usage(KB)',
	total_scheduled_time,reads,writes,logical_reads
	FROM tb a
	INNER JOIN master.sys.dm_exec_sessions b ON a.session_id=b.session_id
)

SELECT a.*,connect_time,client_tcp_port,client_net_address
FROM tb1 a INNER JOIN master.sys.dm_exec_connections b ON a.session_id=b.session_id
GO


-- 查出互相死锁的进程信息
USE MASTER
GO
SELECT a.*
FROM	sysprocesses a, sysprocesses b
WHERE	a.blocked > 0 and
            b.blocked > 0 and
            a.spid != b.spid and
            a.blocked = b.spid and
            b.blocked = a.spid
GO