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

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。