Automate Job to Kill process that heavily accces the SQL machine

Hello friends,

I do have a 'SQL user' who runs a lot of queries that causes performance issues to the database machine. I want to write a query that would check the CPU,Memory I/O usage from the systems table.

I feel i can use the following

Sp_who2 'active'

Can some one help me to write a query that can identify the heaviest usage spid and Kill that process. I want to automate it in such a way that the SQL job runs for every 5 minutes which cheks for this actively running process and Kills the user.

Please do help me

Thanks
Eben

Below is useful script written by SQL DBA in one of the forums which kills processes hanging around more than 5min , you tweak that further.
But make sure you test it further for your needs.
use master

DECLARE @spid INT, @dbname sysname,
@cnt INT,
@sql VARCHAR(255)

SELECT @spid = MIN(spid),@cnt = COUNT(*)
FROM master..sysprocesses
WHERE dbid = DB_ID(@dbname) AND spid != @@SPID
AND LAST_BATCH > DATEADD (mi, -5, getdate())
and status = 'sleeping'

WHILE @spid IS NOT NULL

BEGIN

SET @sql = 'KILL ' + RTRIM(@spid)

EXEC ( @sql )

SELECT @spid = MIN(spid),@cnt = COUNT(*)
FROM master..sysprocesses
WHERE dbid = DB_ID(@dbname) AND spid != @@SPID
AND LAST_BATCH > DATEADD (mi, -5, getdate())
and status = 'sleeping'

END

Regards
Sat

Hi Babu,
Thanks a lot ,off late I am afraid as Killing the spid may result in Rollback and would make me to restart the SQL services. I am using SQL2000 Enterprise and am ruled out using DMV's too.
Can you help me to tune the query by removing the Kill process , however alerting me by sending mail by using xp_sendmail concept.
Kindly help me to tune the query, I want a mail to be received with the process ID and not to Kill the user.
Thanks