CREATE PROCEDURE [dbo].[sp_who_lock1116] AS
--exec sp_who_lock1116
begin
declare @spid int,@bl int,
@intTransactionCountOnEntry int,
@intRowcount int,
@intCountProperties int,
@intCounter int
declare @sql varchar(1000)
create table #tmp_lock_who(
id int identity(1,1),
spid smallint,
bl smallint)
if @@error<>0 return @@error
insert into #tmp_lock_who(spid,bl)
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
if @@error<>0 return @@error
--找到临时表的记录数
select @intCountProperties=count(*),@intCounter=1
from #tmp_lock_who
if @@error<>0 return @@error
if @intCountProperties=0
select '现在没有阻塞信息' as message
--循环开始
while @intCounter<=@intCountProperties
begin
select @spid=spid,@bl=bl
from #tmp_lock_who where id=@intCounter
begin
if @spid=0
begin
select '引起数据库死锁的是：'+cast(@bl as varchar(10))+'进程号，其执行的SQL语法如下'
end
else
begin
select '进程号spid：'+cast(@spid as varchar(10))+'被'+'进程号spid'+cast(@bl as varchar(10))+'阻塞，其执行的SQL语法如下'
end
DBCC inputbuffer(@bl)
set @sql='kill '+cast(@bl as varchar(10))
exec(@sql)
end
set @intCounter=@intCounter+1
end
drop table #tmp_lock_who
return 0
end
经常性的死锁，如果无法彻底解决的话，可建个计划定时执行以上存储过程
