November 30, 2017 at 6:51 am
I have a SQL Agent job that is scheduled to run monthly. The job has 12 steps. On schedule the job runs steps 1 through 5. Sometimes we manually start the job at step 6, then it runs through step 12. I have been trying to write a query that returns job history for runs where it started at step 1 and succeeded but am not always getting the correct results. Is there a way to do this? Thanks.
November 30, 2017 at 7:02 am
Yes, it should be possible, using the date comparisons for the start and end dates of the overall job (determined from Step 0 in the job history table) and looking to see if there's a history record for that job between those dates/times for Step 1.
--edit--
There may be a better way to do this, but here's something that seems to work as a first attempt:
USE msdb;
SELECT jh.*
FROM sysjobhistory jh
WHERE
EXISTS -- is there a step 1 in the appropriate timespan for the job?
(
SELECT *
FROM sysjobhistory jhin
WHERE
jh.job_id = jhin.job_id
AND jhin.step_id = 1
AND dbo.agent_datetime(jhin.run_date, jhin.run_time) >= dbo.agent_datetime(jh.run_date, jh.run_time)
AND dbo.agent_datetime(jhin.run_date, jhin.run_time) <= DATEADD(SECOND, (jh.run_duration) % 100 + 60 * (jh.run_duration / 100) % 100 + 3600 * (jh.run_duration / 10000) % 100 + 864000 * (jh.run_duration / 1000000), dbo.agent_datetime(jh.run_date, jh.run_time))
)
AND EXISTS -- is step zero for the job giving run_status 1 (successful)?
(
SELECT *
FROM sysjobhistory jhin
WHERE
jh.job_id = jhin.job_id
AND jhin.step_id = 0
AND jhin.run_status = 1
AND dbo.agent_datetime(jhin.run_date, jhin.run_time) >= dbo.agent_datetime(jh.run_date, jh.run_time)
AND dbo.agent_datetime(jhin.run_date, jhin.run_time) <= DATEADD(SECOND, (jh.run_duration) % 100 + 60 * (jh.run_duration / 100) % 100 + 3600 * (jh.run_duration / 10000) % 100 + 864000 * (jh.run_duration / 1000000), dbo.agent_datetime(jh.run_date, jh.run_time))
)
Thomas Rushton
blog: https://thelonedba.wordpress.com
November 30, 2017 at 8:14 am
Thank you! That worked for me.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply