--本脚本用来查询和删除SQL服务器死锁
--如果发现了死锁,请试着先KILL一个死锁了的进程
--本脚本只限数据库管理员使用
--by YWL 2005-03-01
----------------------------------------------
--1、查询死锁
USE master
GO
DECLARE @spid int,@bl int
DECLARE s_cur CURSOR FOR
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
OPEN s_cur
FETCH NEXT FROM s_cur INTO @spid,@bl
WHILE @@FETCH_STATUS = 0
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 )
FETCH NEXT FROM s_cur INTO @spid,@bl
END
CLOSE s_cur
DEALLOCATE s_cur
--2、显示进程
EXEC sp_who2
--3、杀掉死锁,请修改下列语句进程号,再执行 kill xxx
-- KILL 78
*/
--如果发现了死锁,请试着先KILL一个死锁了的进程
--本脚本只限数据库管理员使用
--by YWL 2005-03-01
----------------------------------------------
--1、查询死锁
USE master
GO
DECLARE @spid int,@bl int
DECLARE s_cur CURSOR FOR
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
OPEN s_cur
FETCH NEXT FROM s_cur INTO @spid,@bl
WHILE @@FETCH_STATUS = 0
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 )
FETCH NEXT FROM s_cur INTO @spid,@bl
END
CLOSE s_cur
DEALLOCATE s_cur
--2、显示进程
EXEC sp_who2
--3、杀掉死锁,请修改下列语句进程号,再执行 kill xxx
-- KILL 78
*/