Best Way to Monitor SQL Server Agent Job Step

Question

What is the best practice to monitor SQL Server job steps?

I have a single job with 50 steps. Sometimes, some of the steps are taking longer to complete. I want to have a monitoring process that notifies me when a step takes longer than usual. The problem is that I'm not able to find any information about job steps while a step is running and before they get completed (at least not in sysjobsteps and sysjobs). In particular, I'm interested about the step_id, step_name and step_start_time.

I would appreciate any ideas.

Answer 1

The system table you're looking for is actually dbo.sysjobhistory, and specifically the run_date, run_time, and run_duration columns:

run_date - Date the job or step started execution. For an In Progress history, this is the date/time the history was written.

run_time - Time the job or step started in HHMMSS format.

run_duration - Elapsed time in the execution of the job or step in HHMMSS format.

I do realize you asked for information while a job step is in process, but as per the Microsoft Books Online:

In most cases the data is updated only after the job step completes and the table typically contains no records for job steps that are currently in progress, but in some cases underlying processes do provide information about in progress job steps.

So only in certain cases will you have access to that information.

Alternatively, I can only think to recommend monitoring the running queries on your server instead, if you want a more realtime approach. You can use tools like Adam Machanic's sp_WhoIsActive to help you accomplish that. You may find it difficult to correlate the job step itself to the query it's executing, unless the query is tagged with an identifiable comment in the beginning, but I also realize this is not ideal either.