SQL Agent: Set a Max Execution Time

Question

Afternoon. I have several SQL Agent jobs running on an MS 2K8 BI server, some of them on a daily basis, others hourly, and one every two minutes (a heartbeat monitor for another process). There is also an app which imports data every few minutes, around the clock. Occasionally some combination of updates and reports collide and one or another hangs for a half hour or more, instead of the usual 60 seconds.

While I need to get to the root of these race conditions, in the meantime I'd like to set certain jobs to automatically die after, say, five minutes. I can do this in SSIS or a Windows scheduled task, but I don't see any way to do so in SQL Agent. Is this possible, or do I need to wrap the task in an SSIS package to get this kind of control?

---

FYI, here's the SQL Agent job I ended up using:

DECLARE @Cancelled BIT
EXEC dbo.CancelJob @JobName = 'ETL - Daily', @Cancelled = @Cancelled OUT

IF @Cancelled = 1
    BEGIN
    DECLARE @Success INT
    EXEC @Success = msdb..sp_send_dbmail
        @profile_name = 'Reporting',
        @recipients = '[email protected]',
        @subject = 'Cancelled Daily ETL'
    IF @Success <> 0 RAISERROR('An error occurred while attempting to send an e-mail.', 16, @Success)
    END

...and here's the code behind CancelJob:

CREATE PROCEDURE dbo.CancelJob(@JobName VARCHAR(100), @OwnerName VARCHAR(100) = NULL, @Cancelled BIT OUT)
AS BEGIN
    IF @OwnerName IS NULL SET @OwnerName = SUSER_NAME()
    SET @Cancelled = 0

    CREATE TABLE #JobInfo
        ( 
        Job_ID  UNIQUEIDENTIFIER,
        Last_Run_Date   INT,
        Last_Run_Time   INT,
        Next_Run_Date   INT,
        Next_Run_Time   INT,
        Next_Run_Schedule_ID    INT,
        Requested_To_Run    INT,
        Request_Source  INT,
        Request_Source_ID   VARCHAR(100),
        Running INT,  -- This is the only field we want (sigh)
        Current_Step    INT,
        Current_Retry_Attempt   INT,
        State   INT
        )
    INSERT INTO #JobInfo
        EXEC xp_sqlagent_enum_jobs 1, @OwnerName

    DECLARE @Running INT = (SELECT Running FROM #JobInfo AS JI INNER JOIN msdb..sysjobs_view AS J ON JI.Job_ID = J.job_id WHERE J.name = @JobName)
    IF @Running = 1
        BEGIN
        BEGIN TRY
            EXEC msdb..sp_stop_job @job_name = @JobName
            SET @Cancelled = 1
        END TRY
        BEGIN CATCH
            -- If an error occurs, it is *probably* because the job finished before we could cancel it, which is fine
        END CATCH
        END
END
GO

xp_sqlagent_enum_jobs was the trick to avoid the uncatchable error.

Solution

I have never had to do this frequently, so there may be better long-term solutions, but I have created a second job to stop the first on the rare occasions that I had to perform this task. I just used the sp_stopjob procedure to do this.

Comments

[Admin]

My first thought was that this would entail creating an evil twin for every job, with a schedule running X minutes after the first, but better will be to put calls to msdb..sp_stop_job at the beginning of each important job, to kill any other running jobs which could interfere with the one which is about to start. Not bad. Thanks, Doug!

[Admin]

It seems that sp_stop_job throws an uncatchable exception (WTF!?) if the job is not running. My workaround is to put the stop in a separate step in the job, with the "on error" action of "go to the next step." The obvious solution would be to check to see if the job is running before trying to kill it, but AFAIK sp_help_job is the only way to know if a job is currently running, and it can't be used with INSERT EXEC. Anyone have a better idea?