November 2, 2015 at 8:44 pm
I use this code to check if the specific SQL job is still running before I perform a task. However, sometimes it returns multiple records.
SELECT * FROM msdb.dbo.sysjobs_view sjv INNER JOIN msdb.dbo.sysjobactivity sja ON (sjv.job_id = sja.job_id) WHERE sjv.enabled = 1 AND sja.run_requested_date IS NOT NULL AND sja.stop_execution_date IS NULL AND sjv.name = '<job name>'
the task that the job run is running profiler trace. I guess that where clause is not a reliable one to check if the job is still executing.
I tried adding AND last_executed_step_date is null but there are cases that the column has value but the job is still running.
any idea why?
:-):cool:
November 4, 2015 at 6:20 am
Hi
If job has a few steps to do, the last_executed_step_date will be not null for already finished.
Br.
Mike
November 6, 2015 at 9:41 am
Yeah, that drives you crazy until you figure it out, since SQL doesn't always clear out the job activity queue. You have to join to another control table to make sure you are only looking at jobs for the current session of SQL Agent:
...
INNER JOIN msdb.dbo.sysjobactivity sja ON (sjv.job_id = sja.job_id)
INNER JOIN ( SELECT MAX(session_id) AS session_id FROM msdb.dbo.syssessions ) ss ON ss.session_id = sja.session_id
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply