I'm looking for a way to monitor our ETL jobs, which are comprised of a number of SQL Agent jobs with multiple job steps. My searching has shown when the jobs start, but not when the steps start.
Is there a way to query the currently running job steps and see what time each *step* started?
December 28, 2020 at 9:56 pm
Something like this:
DROP TABLE IF EXISTS #xp_results;
CREATE TABLE #xp_results
(
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', NULL;
SELECT #xp_results.job_id, sysjobs.name AS job_name,
current_step, sysjobsteps.step_name,
job_state,
sysjobsteps.last_run_date,
sysjobsteps.last_run_time
FROM #xp_results
INNER JOIN msdb.dbo.sysjobs ON sysjobs.job_id = #xp_results.job_id
INNER JOIN msdb.dbo.sysjobsteps ON sysjobsteps.job_id = #xp_results.job_id AND sysjobsteps.step_id = #xp_results.current_step
where #xp_results.running = 1;
Modify joins/columns/filters as needed.
December 29, 2020 at 2:11 pm
Thanks! I've looked at the results. The Last_Run_Date and Last_Run_Time are returning the *Last time the step ran, not the date/time that the *Currently running step started running. So, this is useful for historical analysis, but not for monitoring the length of time the current job step is taking.
Just to pay back a little of your help, there is a built-in function for turning the run date and run time fields into a datetime field:
msdb.dbo.agent_datetime( sysjobsteps.last_run_date, sysjobsteps.last_run_time )
If anyone knows where I can find the *Start time of the currently running job step, I would greatly appreciate your help!
December 29, 2020 at 4:00 pm
Not directly. But sysjobhistory has the time each step started and the duration of the step. Of course that means that the row for a step cannot be written to the history table until the step has completed.
Say your job is currently on step5. You can see, in sysjobhistory, when step4 started and its duration, which when added should be the start time for step5 (in theory it could be the start of a WAITFOR or some other delaying mechanism, but it's still when control passed to that step).
So if you look up the last step4 for that job in the sysjobhistory log table, you should be able to do the calcs you want.
The potential gotcha is that sysjobhistory doesn't provide any linking value to relate steps from a given job run to each other. Thus, in theory, you have to be careful that the job hasn't restarted and again gotten to step5 so that you're actually looking at the wrong time. Probably not likely for the vast majority of jobs, but if a job suddenly crashes, for certain jobs and steps it could theoretically happen.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 29, 2020 at 7:40 pm
Thanks, Scott. I appreciate your feedback.
I was hoping not to have to go the route of looking at the previous job step, for a few of reasons:
If a job has only 1 step, then that step started when the job itself started, and you already know how to tell that.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply