July 23, 2013 at 2:08 am
Weird one
SQL 2008 Standard SP2 on Windows 2003
The SQL below checks to see if a given job ('Production DB Backup.Check DB Integrity") is running and issues a sp_stop_job command if it is.
(I know - you shouldn't be stopping an integrity check etc etc ..)
Anyway, here it is below
IF EXISTS (
SELECT 1
FROM msdb.dbo.sysjobs_view job
INNER JOIN msdb.dbo.sysjobactivity activity ON (job.job_id = activity.job_id)
WHERE run_Requested_date IS NOT NULL
AND stop_execution_date IS NULL
AND job.NAME LIKE '%Integrity%'
)
BEGIN
SELECT 'here'
EXEC msdb.dbo.sp_stop_job 'Production DB Backup.Check DB Integrity'
END
Works fine when executed in SSMS - as I'd expect, if the job isn't running it does nothing.
Now, when I create a job and put this in as a Transact-SQL step, the job fails as the sp_stop_job gets fired ... even if the job isn't running !
It's as thought the IF EXISTS(..) isn't evaluated correctly ?
Any thoughts folks ?
July 23, 2013 at 2:30 pm
What is the second job called? It doesn't contain '%integrity%' does it?
July 24, 2013 at 12:58 am
If I recall correctly the [sysjobactivity] table isn't updates dynamicly but is updated every 15 minutes or so.
I use the code below if i need to get the active job status (start job if not allready running):
DECLARE @xp_results TABLE (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
INSERT INTO @xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'sa'
if (SELECT running FROM @xp_results where job_id = 'AAAAA041-AAA6-4191-826A-AAA3A3312807') = 0-- job not active?
exec msdb.dbo.sp_start_job @job_name = '{job_name}'
delete from @xp_results
John Huang has a blog written about this: http://www.sqlnotes.info/2012/01/13/are-jobs-currently-running/#more-1194
July 24, 2013 at 1:51 am
Ed B (7/23/2013)
What is the second job called? It doesn't contain '%integrity%' does it?
Ed B, you are as clever as I was dim.
Great spot and thank you very much !
HanShi - thanks for the tip !
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply