All we need is an easy explanation of the problem, so here it is.
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)
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Method 1
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
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0