清理数据库连接线程

清理数据库连接线程

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 )
FETCH NEXT FROM clr_sp_cursor
INTO @spid ;
END
CLOSE clr_sp_cursor;
DEALLOCATE clr_sp_cursor;

end
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/*
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 )
as
begin
if @i = 1 -- Image_name
begin
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 ))
end
else if @i = 2 -- Pid
begin
if patindex ( ' %.exe% ' , @s ) = 0
set @s = stuff ( @s , 1 , patindex ( ' %[0-9]% ' , @s ) - 1 , '' )
else
set @s = ltrim ( substring ( @s , patindex ( ' %.exe% ' , @s ) + 4 , len ( @s )))

select @s = rtrim ( left ( @s , patindex ( ' %[^0-9]% ' , @s ) - 1 ))
end
else if @i = 3 -- Session_name
begin
if patindex ( ' %.exe% ' , @s ) = 0
set @s = stuff ( @s , 1 , patindex ( ' %[0-9]% ' , @s ) - 1 , '' )
else
set @s = ltrim ( substring ( @s , patindex ( ' %.exe% ' , @s ) + 4 , len ( @s )))
select @s = stuff ( @s , 1 , patindex ( ' %[^0-9]% ' , @s ) - 1 , '' ),
@s = rtrim ( left ( @s , patindex ( ' %[0-9]% ' , @s ) - 1 ))
end
else if @i = 4 -- Session#
begin
if patindex ( ' %.exe% ' , @s ) = 0
set @s = stuff ( @s , 1 , patindex ( ' %[0-9]% ' , @s ) - 1 , '' )
else
set @s = ltrim ( substring ( @s , patindex ( ' %.exe% ' , @s ) + 4 , len ( @s )))
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 ))
end
else if @i = 5 -- memory
begin
if patindex ( ' %.exe% ' , @s ) = 0
set @s = stuff ( @s , 1 , patindex ( ' %[0-9]% ' , @s ) - 1 , '' )
else
set @s = ltrim ( substring ( @s , patindex ( ' %.exe% ' , @s ) + 4 , len ( @s )))

select @s = stuff ( @s , 1 , patindex ( ' %[0-9]% ' , @s ) - 1 , '' )
, @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 ))
end
else if @i = 6 -- user_name
select @s = stuff ( @s , 1 , patindex ( ' %[0-9]% ' , @s ) - 1 , '' )
, @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 ( 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 )
else
select @s =right ( @s , charindex ( ' ' , reverse ( @s ), 3 ) - 1 )
return @s
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

posted on 2011-07-08 17:12  Fanr_Zh 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/Amaranthus/archive/2011/07/08/2101210.html