--修改如下
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
begin
select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
end
else
begin
select '进程号SPID:'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID:'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@spid )
end
FETCH NEXT FROM s_cur INTO @spid,@bl
end
CLOSE s_cur
DEALLOCATE s_cur---exec sp_who 52
--kill 32=============================================================================================
------------------------------------------------------------------------------
进程号SPID:8被进程号SPID:40阻塞,其当前进程执行的SQL语法如下(1 row(s) affected)EventType Parameters EventInfo
-------------- ---------- -------------------------------------------------------
Language Event 0 execute pro_mps_create @jhno1 =200711 , @jhno2 =200801 (1 row(s) affected)DBCC execution completed. If DBCC printed error messages, contact your system administrator.
--------------------------------------------------------
引起数据库死锁的是: 40进程号,其执行的SQL语法如下(1 row(s) affected)EventType Parameters EventInfo
-------------- ---------- --------------
Language Event 0 SET NOEXEC OFF(1 row(s) affected)DBCC execution completed. If DBCC printed error messages, contact your system administrator.======================================================
我是个菜鸟,以前从没接触过SQL,现在也只是知道一点点,上面的代码是查询死锁进程ID的,下面是引起死锁的信息,我想知道该如何用上面的代码来杀点引起死锁的ID?关键是杀那个ID?如何识别?跪求高手解答,谢谢!
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
begin
select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
end
else
begin
select '进程号SPID:'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID:'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@spid )
end
FETCH NEXT FROM s_cur INTO @spid,@bl
end
CLOSE s_cur
DEALLOCATE s_cur---exec sp_who 52
--kill 32=============================================================================================
------------------------------------------------------------------------------
进程号SPID:8被进程号SPID:40阻塞,其当前进程执行的SQL语法如下(1 row(s) affected)EventType Parameters EventInfo
-------------- ---------- -------------------------------------------------------
Language Event 0 execute pro_mps_create @jhno1 =200711 , @jhno2 =200801 (1 row(s) affected)DBCC execution completed. If DBCC printed error messages, contact your system administrator.
--------------------------------------------------------
引起数据库死锁的是: 40进程号,其执行的SQL语法如下(1 row(s) affected)EventType Parameters EventInfo
-------------- ---------- --------------
Language Event 0 SET NOEXEC OFF(1 row(s) affected)DBCC execution completed. If DBCC printed error messages, contact your system administrator.======================================================
我是个菜鸟,以前从没接触过SQL,现在也只是知道一点点,上面的代码是查询死锁进程ID的,下面是引起死锁的信息,我想知道该如何用上面的代码来杀点引起死锁的ID?关键是杀那个ID?如何识别?跪求高手解答,谢谢!
过不过去啊?每次把引起死锁的进程kill 但是就是没反应?还是一样的死锁,我想是不是查询那段代码是不是还要改一下那个地方?请高手指点啊!
* Money go my home
* 等 级:
发表于:2007-12-01 12:24:523楼 得分:0
kill 40_________________________________________________SQL Server 2000 和更高版本保留从 1 到 50 的 SPID 值以供内部使用,而 51 或更大的 SPID 值则表示用户会话。Money go my home,看联机说明没有? :D