Script to kill all processes running for more than X hours 1

[JtheRipper]

Hi there,

I want to create a script that I can run anytime to kill all the processes on my SQL Server 2000 instance that have been running for more than i.e. 2 hours. There should not be any processes running this long, but sometimes a processes gets "stuck" and prohibits other processes from finishing. I had a look at sysprocesses and sysjobs, but can't seem to find the necessary info to write a script/procedure to do this.

Any ideas?

Thanks,
J.

 

[vongrunt]

You can query master..sysprocesses, exclude system SPID's and filter rows according to last_batch, dbid, cmd and some other fields. At least in theory - be careful whatever you choose.

FYI there is also undocumented sproc called sp_MSget_current_activity.

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[AshleyRickards]

I'm looking for the same utility. Did you ever find anything?

Ashley L Rickards
SQL DBA

 

[RobertT687]

My company uses this procedure to kill processes running for more than 60 minutes connected to specific databases by a specific userid. It is run as a scheduled job every 15 minutes throughout the business day. Change or omit the loginid and substitute your database names.

create procedure usp_Kill_Idle_Spids
As
Declare @killspid smallint
Declare @killCmd varchar(255)

Declare spid_cursor Cursor For
Select spid
From master.dbo.sysprocesses
Where status = 'sleeping'
And loginame = 'tatgetloginid'
And db_name(dbid) IN ('db1','db2','db3')
And Datediff(mi, login_time,getdate()) > 60
--
OPEN spid_cursor
Fetch Next From spid_cursor Into @killspid
--
WHILE @@FETCH_STATUS = 0
BEGIN
Select @killCmd = 'KILL ' + Cast(@killspid As varchar(3))
EXEC (@killCmd)
Fetch Next From spid_cursor Into @killspid
END
CLOSE spid_cursor
DEALLOCATE spid_cursor
go

 

[AshleyRickards]

Thank you very much! This works great.

Ashley L Rickards
SQL DBA