Long Running Processes

Question

Long Running Processes

DHeath

SSCertifiable

Points: 5200
More actions
December 5, 2018 at 11:00 am

#376581

I am looking for a query to find and kill long running processes...Now i know i will get a lot of people wanting to tell me to look inot the process and see whats going on but mainly they are sleeping process from a application on a report server. The reports should never run longer than 30 minutes so i am trying to set up the query to kill anything that is over 60 minutes. I have found the below code and i understand it BUT i cant see where i can make the changes to a length of time to kill the processes.
any help is appreciated.
USE Master
GO
SET NOCOUNT ON
-- 1 - Variable Declaration
DECLARE @DBID int
DECLARE @CMD1 VARCHAR(8000)
DECLARE @spidNumber int
DECLARE @SpidListLoop int
DECLARE @SpidListTable TABLE
(UIDSpidList int IDENTITY (1,1),
SpidNumber int)
-- 2 - Populate @SpidListTable with the spid information
-- Master, Tempdb, Model, MSDB
INSERT INTO @SpidListTable (SpidNumber)
SELECT spid
FROM Master.dbo.sysprocesses
WHERE DBID NOT IN (1,2,3,4) AND spid > 50 AND spid <>@@spid
ORDER BY spid DESC
-- 3b - Determine the highest UIDSpidList to loop through the records
SELECT @SpidListLoop = MAX(UIDSpidList) FROM @SpidListTable
-- 3c - While condition for looping through the spid records
WHILE @SpidListLoop > 0
BEGIN
-- 3d - Capture spids location
SELECT @spidNumber = spidnumber
FROM @spidListTable
WHERE UIDspidList = @SpidListLoop
-- 3e - String together the KILL statement
SELECT @CMD1 = 'KILL ' + CAST(@spidNumber AS VARCHAR(5))
-- 3f - Execute the final string to KILL the spids i purposely made this print so NOTHING is done accidently
--EXEC (@CMD1)
PRINT (@CMD1)
-- 3g - Descend through the spid list
SELECT @SpidListLoop = @SpidListLoop - 1
END
SET NOCOUNT OFF
GO
Thanks D
DHeath

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

goher2000 SSCertifiable Points: 5479 More actions · Answer 1

Something like that

SELECT 'kill ' + cast(spid as varchar) cmd2kill, *
FROM Master.dbo.sysprocesses
WHERE DBID NOT IN (1,2,3,4) AND spid > 50 AND spid <>@@spid
and program_name='Report Server'
and login_time <= dateadd(hour, -1,getdate())

DHeath SSCertifiable Points: 5200 More actions · Answer 2

Thanks a TON to goher2000 ... That works perfectly and greatly appreciated. I actually just use the code you created and took out the other stuff that i had previously...much more precise and to the point.

DHeath

DHeath SSCertifiable Points: 5200 More actions · Answer 3

How would i make this so it run fully automated in a job. I need to take the "kill 001" and in the end
string them all together so that it runs and does the exec as well

SELECT 'kill ' + cast(spid as varchar) cmd2kill, *
FROM Master.dbo.sysprocesses
WHERE DBID NOT IN (1,2,3,4) AND spid > 50 AND spid <>@@spid
and program_name='Report Server'
and login_time <= dateadd(hour, -1,getdate())
--how to take the select statement and put it in a @CMD2KILL so then i can EXEC @CMD2KILL

Thank you

DHeath

goher2000 SSCertifiable Points: 5479 More actions · Answer 4

enjoy

declare @cmd2kill varchar(10)=null;
declare killproc cursor for
SELECT 'kill ' + cast(spid as varchar)
FROM Master.dbo.sysprocesses
WHERE DBID NOT IN (1,2,3,4) AND spid > 50 AND spid <>@@spid
and program_name='Report Server'
and login_time <= dateadd(hour, -1,getdate())
open killproc
fetch next from killproc into @cmd2kill
while @@FETCH_STATUS=0
begin
print @cmd2kill
--exec(@cmd2kill) -- really want to kill em.
fetch next from killproc into @cmd2kill
end
close killproc
deallocate killproc

DHeath SSCertifiable Points: 5200 More actions · Answer 5

Goher2000...you are much appreciated..

Thanks

DHeath