#MSSQL Auto KILL SPIDs based upon job name

kill_job_spids.sql

DECLARE @spid INT
DECLARE @kill_spid NVARCHAR(100)

DECLARE running_jobs CURSOR FOR

    SELECT 
    req.session_id
    FROM sys.dm_exec_requests req
    LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = req.session_id
    LEFT JOIN msdb.dbo.sysjobs jobs ON SUBSTRING(ISNULL(ses.[program_name],''),CHARINDEX('0x', ISNULL(ses.[program_name],'')) + 18, 16) = SUBSTRING(REPLACE(ISNULL(jobs.[job_id],''), '-',''),17,16)
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
    WHERE jobs.name = 'MyJob'

OPEN running_jobs
FETCH NEXT FROM running_jobs INTO @spid

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @kill_spid = 'kill ' + CAST(@spid AS VARCHAR(50))

    PRINT @kill_spid
    EXECUTE sp_executesql @kill_spid

    FETCH NEXT FROM running_jobs INTO @spid
END

CLOSE running_jobs
DEALLOCATE running_jobs