SQL Server Performance

Automate Job to Kill process that heavily accces the SQL machine

Discussion in 'General DBA Questions' started by Ebenraja, Oct 29, 2009.

  1. Ebenraja Member

    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
  2. techbabu303 New Member

    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
  3. Ebenraja Member

    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

Share This Page