[ACCEPTED]-SQL Server Agent Job Timeout-sql-server-agent

Accepted answer

Score: 13

We do something like the code below as part 6 of a nightly job processing subsystem - it 5 is more complicated than this actually in 4 reality; for example we are processing multiple 3 interdependent sets of jobs, and read in 2 job names and timeout values from configuration 1 tables - but this captures the idea:

    DECLARE @JobToRun NVARCHAR(128) = 'My Agent Job'
DECLARE @dtStart DATETIME = GETDATE(), @dtCurr DATETIME
DECLARE @ExecutionStatus INT, @LastRunOutcome INT, @MaxTimeExceeded BIT = 0
DECLARE @TimeoutMinutes INT = 180 

EXEC msdb.dbo.sp_start_job @JobToRun
SET @dtCurr = GETDATE()
WHILE 1=1
BEGIN
    WAITFOR DELAY '00:00:10'
    SELECT @ExecutionStatus=current_execution_status, @LastRunOutcome=last_run_outcome 
    FROM OPENQUERY(LocalServer, 'set fmtonly off; exec msdb.dbo.sp_help_job') where [name] = @JobToRun
    IF @ExecutionStatus <> 4
    BEGIN -- job is running or finishing (not idle)
        SET @dtCurr=GETDATE()
        IF DATEDIFF(mi, @dtStart, @dtCurr) > @TimeoutMinutes
        BEGIN   
            EXEC msdb.dbo.sp_stop_job @job_name=@JobToRun                   
            -- could log info, raise error, send email etc here
        END
        ELSE
        BEGIN
            CONTINUE
        END
    END
    IF @LastRunOutcome = 1  -- the job just finished with success flag
    BEGIN
        -- job succeeded, do whatever is needed here
        print 'job succeeded'                                   
    END

END

Score: 2

What kind of a job is this? You may want 4 to consider putting the whole job in a TSQL 3 script within a While loop. The condition 2 to check would obviously be the time difference 1 between current time and job start time.

Raj

Source: stackoverflow.com

[ACCEPTED]-SQL Server Agent Job Timeout-sql-server-agent

Looking for T-SQL scripts to delete a SQL Job

SQL Server Agent not running

cron.yaml in Google App Engine: how to schedule task every hour at specific minute?

Delayed execution / scheduling with Redis?

How to cancel Spring timer execution

PHP function that receive a cron string and return the next run timestamp

DAG(directed acyclic graph) dynamic job scheduler

SCHED_FIFO process with priority of 99 gets preempted?

How to schedule a task in Tomcat

Optimized algorithm to schedule tasks with dependency?