007_进程查杀
007_进程查杀
--创建存储过程【p_lockinfo】
----进程死锁查杀
/*
查看：exec p_lock_info 0,1
杀死: exec p_lock_info 1,1
*/
create procedure p_lockinfo
@kill_lock_spid bit=1, --是否杀掉死锁进程。1，杀掉 0，仅显示
@show_spid_if_nolock bit=1 --如果没有死锁进程，是否显示正常进程。 1，显示 0不显示
as
declare @count int,
@s nvarchar(1000),
@i int
select
id=IDENTITY(int,1,1),
标志
,进程ID=spid
,线程ID=kpid
,块进程ID=blocked
,数据库ID=dbid
,数据库名=DB_NAME(dbid)
,用户ID=uid
,用户名=loginame
,累计cpu时间=cpu
,登录时间=login_time
,打开事物数=open_tran
,进程状态=status
,工作站名=hostname
,应用程序名=program_name
,工作站进程ID=hostprocess
,域名=nt_domain
,网卡地址=net_address
into #test_liwz
from
(
select
标志='死锁进程'
,spid
,kpid
,blocked
,dbid
,uid
,loginame
,cpu
,login_time
,open_tran
,status
,hostname
,program_name
,hostprocess
,nt_domain
,net_address
,s1=spid
,s2=0
from
master..sysprocesses
where blocked=0
union all
select
标志=N'l_牺牲品—>'
,spid
,blocked
,dbid
,uid
,kpid
,loginame
,cpu
,login_time
,open_tran
,status
,hostname
,program_name
,hostprocess
,nt_domain
,net_address
,s1=blocked
,s2=1
from
master..sysprocesses
where blocked<>0
) a
order by s1,s2
select @count=@@ROWCOUNT,@i=1
if @count=0 and @show_spid_if_nolock=1
begin
insert #test_liwz
select
标志=N'正常进程'
,spid
,blocked
,dbid
,uid
,kpid
,loginame
,cpu
,login_time
,open_tran
,status
,hostname
,program_name
,hostprocess
,nt_domain
,net_address
,s1=blocked
,s2=1
from
master..sysprocesses
set @count=@@ROWCOUNT
end
if @count>0
begin
create table #test_liwz_1
(
id int identity(1,1)
,a nvarchar(30)
,b int
,EventInfo nvarchar(255)
)
if @kill_lock_spid=1
begin
declare @spid varchar(10)
,@标志 varchar(10)
while @i<=@count
begin
select @spid=进程ID,@标志=标志 from #test_liwz where id=@i
insert #test_liwz_1 exec('dbcc inputbuffer('+@spid+')')
if @@ROWCOUNT=0
insert #test_liwz_1(a) values(null)
if @标志='死锁进程' exec('kill'+@spid)
set @i=@i+1
end
end
else
while @i<=@count
begin
select @s='dbcc inputbuffer('+CAST(进程ID as varchar)+')' from #test_liwz where id=@i
insert #test_liwz_1 exec(@s)
if @@ROWCOUNT=0
set @i=@i+1
end
select a.*,进程的sql语句=b.EventInfo
from #test_liwz a join #test_liwz_1 b on a.id=b.id
order by 进程ID
end
set nocount off
go
