本篇文章转自：链接：http://www.chinahtml.com/0611/11647833988945.html
查看当前进程,或死锁进程,并能自动杀掉死进程：
处理死锁
查看当前进程,或死锁进程,并能自动杀掉死进程。因为是针对死的,所以如果有死锁进程,只能查看死锁进程。当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程。
调用示例
执行：Exec p_lockinfo
具体的存储过程如下：
CREATE PROC p_lockinfo
@kill_lock_spid BIT = 1 , --是否杀掉死锁的进程,1 杀掉, 0 仅显示
@show_spid_if_nolock BIT = 1 --如果没有死锁的进程, 是否显示正常进程信息,1 显示,0 不显示
AS
DECLARE @count INT ,
@s NVARCHAR(MAX) ,
@i INT
SELECT id = IDENTITY( INT,1,1 ),
标志 ,
进程ID = spid ,
线程ID = kpid ,
块进程ID = blocked ,
数据库ID = dbid ,
数据库名 = DB_NAME(dbid) ,
用户ID = uid ,
用户名 = loginame ,
累计CPU时间 = cpu ,
登陆时间 = login_time ,
打开事务数 = open_tran ,
进程状态 = status ,
工作站名 = hostname ,
应用程序名 = program_name ,
工作站进程ID = hostprocess ,
域名 = nt_domain ,
网卡地址 = net_address
INTO #t
FROM ( SELECT 标志 = '死锁的进程' ,
spid ,
kpid ,
a.blocked ,
dbid ,
uid ,
loginame ,
cpu ,
login_time ,
open_tran ,
status ,
hostname ,
program_name ,
hostprocess ,
nt_domain ,
net_address ,
s1 = a.spid ,
s2 = 0
FROM master..sysprocesses a
JOIN ( SELECT blocked
FROM master..sysprocesses
GROUP BY blocked
) b ON a.spid = b.blocked
WHERE a.blocked = 0
UNION ALL
SELECT '|_牺牲品_>' ,
spid ,
kpid ,
blocked ,
dbid ,
uid ,
loginame ,
cpu ,
login_time ,
open_tran ,
status ,
hostname ,
program_name ,
hostprocess ,
nt_domain ,
net_address ,
s1 = blocked ,
s2 = 1
FROM master..sysprocesses a
WHERE blocked <> 0
) a
ORDER BY s1 ,
s2
SELECT @count = @@rowcount ,
@i = 1
IF @count = 0
AND @show_spid_if_nolock = 1
BEGIN
INSERT #t
SELECT 标志 = '正常的进程' ,
spid ,
kpid ,
blocked ,
dbid ,
DB_NAME(dbid) ,
uid ,
loginame ,
cpu ,
login_time ,
open_tran ,
status ,
hostname ,
program_name ,
hostprocess ,
nt_domain ,
net_address
FROM master..sysprocesses
SET @count = @@rowcount
END
IF @count > 0
BEGIN
CREATE TABLE #t1
(
id INT IDENTITY(1, 1) ,
a NVARCHAR(30) ,
b INT ,
EventInfo NVARCHAR(max)
)
IF @kill_lock_spid = 1
BEGIN
DECLARE @spid VARCHAR(10) ,
@标志 VARCHAR(10)
WHILE @i <= @count
BEGIN
SELECT @spid = 进程ID ,
@标志 = 标志
FROM #t
WHERE id = @i
INSERT #t1
--显示从客户机发送到服务器的最后一个语句
EXEC ( 'dbcc inputbuffer(' + @spid + ')'
)
IF @标志 = '死锁的进程'
EXEC('kill '+@spid)
--在此处将Event 及Event写入日志；并抛出异常，提示用户操作失败，并等待重试
SET @i = @i + 1
END
END
ELSE
WHILE @i <= @count
BEGIN
SELECT @s = 'dbcc inputbuffer('
+ CAST(进程ID AS VARCHAR) + ')'
FROM #t
WHERE id = @i
INSERT #t1
EXEC ( @s
)
SET @i = @i + 1
END
SELECT a.* ,
进程的SQL语句 = b.EventInfo
FROM #t a
JOIN #t1 b ON a.id = b.id
END
go
