How 'bout this as an option? I copied and modified a script found that might allow me to stop a 'hung' job long after it should have completed.
I could add it as a last step to the job with a WAITFOR DELAY. Every time the job runs, it checks for a 'hung' status, and stops it.
All comments and/or modifications welcome.
--OPTION# 1
DECLARE
@JobID UNIQUEIDENTIFIER
--jobid from sysjobs for your job i question'
SET
@JobID = '573878F9-CFB3-417B-B802-7A6B914A2204'
IF OBJECT_ID('tempdb.dbo.#JobStatus') IS NOT NULL
DROP TABLE #JobStatus
CREATE TABLE #JobStatus
(
Job_IDUNIQUEIDENTIFIER,
Last_Run_Date INT,
Last_Run_Time INT,
Next_Run_Date INT,
Next_Run_Time INT,
Next_Run_Schedule_IDINT,
Requested_To_RunINT,
Request_Source INT,
Request_Source_IDVARCHAR(100),
RunningINT,
Current_Step INT,
Current_Retry_AttemptINT,
StateINT
)
-- Retrieve results of last job run
INSERT INTO #JobStatus
EXEC master.dbo.xp_sqlagent_enum_jobs 0,sa,@JobID
--Select * from #JobStatus
-- Check to see if job is running
IF
(
SELECT COUNT(*) FROM #JobStatus
WHERE Running = 1
) > 0
BEGIN
--Do your work here
WAITFOR DELAY '00:05:00'
Execsp_stop_job @Job_Id = @JobID
END
ELSE
BEGIN
-- Job is not running
END
DROP TABLE#JobStatus