SQL Server 查看进程阻塞及处理
修改或删除数据前先备份,先备份,先备份(重要事情说三遍)!
1、首先,查看线程,分析是否存在阻塞进程,blocked>0都是当前被阻塞的进程
SELECT * FROM sysprocesses where blocked >0 order by blocked ;
2、找到被阻塞的线程后,想要继续查看进程被谁阻塞,分析导致阻塞的源头
SELECT * FROM SYSPROCESSES WHERE spid =spid_no(这是你要分析的进程ID)
3、查看此进程执行的SQL 是哪个,查找问题原因
dbcc inputbuffer(spid);
4、如果想要结束当前进程,KILL 掉当前导致阻塞的SQL
kill spid
以上是手工分析,当前生产有哪些阻塞并自动分析出来源头,我下面提供一个自己整理的自动分析过程,亲测有效,可直接使用:
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