清理資料庫連接配接線程

CREATE proc usp_clr_Session

as

begin

DECLARE @spid INT,@strSql VARCHAR(200),@pid int

if object_id('tempdb..#temp') is not null

drop table #temp

create table #temp

(

id int identity(1,1) not null

,Proc_info varchar(8000) null

)

--insert data into temp table

insert into #temp(Proc_info)

exec master..xp_cmdshell 'tasklist /v /fi "imagename eq clinkivr.exe"'

--delete the wrong records

delete from #temp

where Proc_info is null or Proc_info like '%==========%' or ID=2

select @pid = isnull(max(dbo.F_split_V2(Proc_info,2)) ,-1)

from #temp

print (@pid)

DECLARE clr_sp_cursor CURSOR FOR

SELECT SPID

FROM master..sysprocesses

WHERE cmd = 'AWAITING COMMAND' AND dbid >4 AND status = 'sleeping' and hostprocess = @pid

OPEN clr_sp_cursor;

FETCH NEXT FROM clr_sp_cursor

INTO @spid;

WHILE @@FETCH_STATUS = 0

BEGIN

SET @strSql = 'kill ' + RTRIM(@spid)

PRINT (@strsql)

EXEC (@strsql)

INTO @spid;

END

CLOSE clr_sp_cursor;

DEALLOCATE clr_sp_cursor;

end

GO

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

/*

select * FROM master..sysprocesses where hostprocess = 3368 order by cpu desc

master..xp_cmdshell 'tasklist /v'*/

--ALTER a splite function

Create function F_split_V2(@S nvarchar(4000),@i int)

returns nvarchar(100)

if @i=1--Image_name

if patindex('%.exe%',@s)=0

set @s=rtrim(left(@s,patindex('%[0-9]%',@s)-1))

else

set @s=rtrim(left(@s,patindex('%.exe%',@s)+4))

else if @i=2--Pid

set @s=stuff(@s,1,patindex('%[0-9]%',@s)-1,'')

set @s=ltrim(substring(@s,patindex('%.exe%',@s)+4,len(@s)))

select @s=rtrim(left(@s,patindex('%[^0-9]%',@s)-1))

else if @i=3--Session_name

select @s=stuff(@s,1,patindex('%[^0-9]%',@s)-1,''),

@s=rtrim(left(@s,patindex('%[0-9]%',@s)-1))

else if @i=4--Session#

select @s=stuff(@s,1,patindex('%[^0-9]%',@s)-1,'')

,@s=stuff(@s,1,patindex('%[0-9]%',@s)-1,'')

,@s=rtrim(left(@s,patindex('% %',@s)-1))

else if @i=5--memory

select @s=stuff(@s,1,patindex('%[0-9]%',@s)-1,'')

,@s=stuff(@s,1,patindex('%[^0-9]%',@s)-1,'')

,@s=stuff(@s,1,patindex('%[1-9]%',@s)-1,'')

,@s=rtrim(left(@s,patindex('%k%',@s)-1))

else if @i=6--user_name

select @s=stuff(@s,1,patindex('%[0-9]%',@s)-1,'')

,@s=rtrim(substring(@s,patindex('%\%',@s)+1,18))

else if @i=7--cup_time

select

@s=substring(@s,patindex('%[0-9]:[0-9][0-9]:[0-9][0-9]%',@s),8)

select @s=right(@s,charindex(' ',reverse(@s),3)-1)

return @s

SET QUOTED_IDENTIFIER OFF