Job to stop other job at specific time

August 13, 2021 Andy DBA Stack Exchange, SQL Agent 0

clip art of a double-quote character

Question

We have a job which imports data daily from a remote SQL Server every night, I’ll call it Import-Job. Most of the time this job runs through in about 15 minutes and everything is fine but due to a rather unstable VPN connection sometimes the job takes a lot more time.

We have a designated time window of 1.5h for this job and we can’t risk to let that Import-Job surpass this window (other jobs and production beginning at some point).

We stop the Import-Job at the end of the time window with another job which executes the following TSQL statement:

IF EXISTS (
            SELECT 1 
            FROM msdb.dbo.sysjobs J 
            JOIN msdb.dbo.sysjobactivity A 
            ON A.job_id=J.job_id 
            WHERE J.name=N'Import-Job' 
            AND A.run_requested_date IS NOT NULL 
            AND A.stop_execution_date IS NULL
    )
    BEGIN
            EXEC msdb.dbo.sp_stop_job
            N'Import-Job';
        PRINT 'The job was stopped.'
    END
ELSE
    BEGIN
            PRINT 'The job is not running.'
    END

This does work but it attempts to stop the Import-Job every time, even though the Import-Job is not running, resulting in a error in the Job History:

QLServerAgent Error: Request to stop job Import-Job (from User ServiceUser) refused because the job is not currently running.

What did I miss in this statement, how do I get this T-SQL Script working so it won’t attempt to stop the Import-Job if it is not running? (I want to get rid of the errors)

asked 2021-08-13 by Manu

Answer

If you run the SELECT in your IF EXISTS, I think you’ll find that it returns at least one (maybe several) rows, even when your job isn’t running

SELECT 1 
FROM msdb.dbo.sysjobs J 
JOIN msdb.dbo.sysjobactivity A 
ON A.job_id=J.job_id 
WHERE J.name=N'Import-Job' 
AND A.run_requested_date IS NOT NULL 
AND A.stop_execution_date IS NULL

The reason for this is that if the SQL Agent process terminates unexpectedly while the job is running, it will never populate the end date (this can happen when restarting the server while the job is running). Having old jobs that show A.run_requested_date IS NOT NULL AND A.stop_execution_date IS NULL should be common & expected in your code.

Many people use xp_sqlagent_enum_jobs to get the job current status, however this is an undocumented extended procedure, so my preference is to not use it. I explain further why I dislike xp_sqlagent_enum_jobs in this blog post.

To accomplish approximately what xp_sqlagent_enum_jobs does using T-SQL, I use something like this:

    SELECT TOP 1
        IsRunning = CASE WHEN ja.job_id IS NOT NULL AND ja.stop_execution_date IS NULL THEN 1 ELSE 0 END,
        LastRunTime = ja.start_execution_date,
        NextRunTime = ja.next_scheduled_run_date,
        LastJobStep = js.step_name,
        JobOutcome = CASE 
                        WHEN ja.job_id IS NOT NULL AND ja.stop_execution_date IS NULL THEN 'Running'
                        WHEN run_status = 0 THEN 'Failed'
                        WHEN run_status = 1 THEN 'Succeeded'
                        WHEN run_status = 2 THEN 'Retry'
                        WHEN run_status = 3 THEN 'Cancelled'
                    END
    FROM msdb.dbo.sysjobs j
    LEFT JOIN msdb.dbo.sysjobactivity ja 
        ON ja.job_id = j.job_id
        AND ja.run_requested_date IS NOT NULL
        AND ja.start_execution_date IS NOT NULL
    LEFT JOIN msdb.dbo.sysjobsteps js
        ON js.job_id = ja.job_id
        AND js.step_id = ja.last_executed_step_id
    LEFT JOIN msdb.dbo.sysjobhistory jh
        ON jh.job_id = j.job_id
        AND jh.instance_id = ja.job_history_id
    WHERE j.name = @JobName
    ORDER BY ja.start_execution_date DESC;

This xp_agent_enum_jobs alternative is available on GitHub as a function, as part of my open-source DBA database.

Using the function, your Agent job step would look something like this:

IF EXISTS (
            SELECT 1 
            FROM msdb.dbo.sysjobs j
            CROSS APPLY dbo.AgentJob_Status (j.name) sts
            WHERE j.name = 'Import-Job'
            AND sts.IsRunning = 1
    )
    BEGIN
            EXEC msdb.dbo.sp_stop_job
            N'Import-Job';
        PRINT 'The job was stopped.'
    END
ELSE
    BEGIN
            PRINT 'The job is not running.'
    END

Or if you want to just pull the necessary code out of the function & put it inline in your job step, you could do something like this:
Note that the major functional difference (to resolve the "old rows" problem mentioned above) is to add a TOP 1...ORDER BY to the SELECT:

IF EXISTS (
            SELECT TOP 1 1 
            FROM msdb.dbo.sysjobs j
            LEFT JOIN msdb.dbo.sysjobactivity ja 
                ON ja.job_id = j.job_id
                AND ja.run_requested_date IS NOT NULL
                AND ja.start_execution_date IS NOT NULL
            LEFT JOIN msdb.dbo.sysjobsteps js
                ON js.job_id = ja.job_id
                AND js.step_id = ja.last_executed_step_id
            LEFT JOIN msdb.dbo.sysjobhistory jh
                ON jh.job_id = j.job_id
                AND jh.instance_id = ja.job_history_id
            WHERE j.name = 'Import-Job'
            AND ja.job_id IS NOT NULL 
            AND ja.stop_execution_date IS NULL
            ORDER BY ja.start_execution_date DESC
    )
    BEGIN
            EXEC msdb.dbo.sp_stop_job
            N'Import-Job';
        PRINT 'The job was stopped.'
    END
ELSE
    BEGIN
            PRINT 'The job is not running.'
    END

answered 2021-08-13 by Andy Mallon