查询死锁脚本:
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