sql server 表死锁查杀脚本

查询死锁脚本：

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 

exec sp_who2 

 

杀死锁脚本：

select * from master.dbo.sysprocesses 
where spid>50 
and waittype = 0x0000 
and waittime = 0 
and status = 'sleeping' 
and last_batch < dateadd(minute, -10, getdate()) 
and login_time < dateadd(minute, -10, getdate()) 

declare hcforeach cursor global 
for 
select 'kill ' + rtrim(spid) from master.dbo.sysprocesses 
where spid>50 
and waittype = 0x0000 
and waittime = 0 
and status = 'sleeping' 
and last_batch < dateadd(minute, -60, getdate()) 
and login_time < dateadd(minute, -60, getdate()) 
exec sp_msforeach_worker '?'

 备用方案：

--查询死锁
SELECT request_session_id spid,OBJECT_NAME(resource_associated_entity_id)tableName
FROM sys.dm_tran_locks
WHERE resource_type='OBJECT '

--杀死死锁
KILL 155

--显示死锁相关信息
exec sp_who2 137