Use This Process to Kill sleeping Threads in sql
CREATE proc [dbo].[KillProcess]
as
begin
declare @msrno1 varchar(10) ,@spid varchar(110)='',@count int =0,
@mysql varchar(100)=''
DECLARE C21 CURSOR FOR select spid from sysprocesses where status = 'sleeping' and cmd='AWAITING COMMAND' order by spid
OPEN C21
FETCH FROM C21 INTO @msrno1
WHILE @@FETCH_STATUS = 0 BEGIN
set @count=0
set @mysql=''
select @count= count(1) from sysprocesses where spid=@msrno1 and status = 'sleeping' and cmd='AWAITING COMMAND'
if @count>0
begin
set @mysql= 'kill '+ cast(@msrno1 as varchar(10))
exec( @mysql)
end
FETCH FROM C21 INTO @msrno1
END
CLOSE C21
DEALLOCATE C21
Select 'Done'
end
0 comments:
Post a Comment