Is there a way to get instance_id from msdb.dbo.sysjobhistory during the execution of a SQL Job step?

Question

I'm writing a logging process that will be stored in tables so that the information can be easily referenced and reported. Most of the structure already exists in the system tables for the SQL jobs (msdb.dbo.sysjobs, msdb.dbo.sysjobsteps, etc); however, I want more detail for each execution of a package, so I'm implementing a logging system that will keep track of each task in the package. Basically, for each task (Data Flow, Execute SQL Task, etc), a detailed, custom description of what has occurred will be stored. More specifically, in the event of a failure, the name of the task that failed and the reason for the failure can be quickly determined.

Tying each log record to the ID of the job and step is not an issue, those already exist and are easily captured. The instance_id generated in msdb.dbo.sysjobhistory, however, does not exist until after the execution of the step. Getting that ID doesn't seem possible during the execution of the package.

By the way, I tested when a record is inserted into msdb.dbo.sysjobhistory by creating a job and running a single step with the following T-SQL code:

WAITFOR DELAY '00:01:00'

The goal was to see when the instance_id is generated and inserted into msdb.dbo.sysjobhistory, which occurred after the step had completed.

Answer 1

If you create dummy steps between each real step, will that work?

So if step1 is Start Import, ans step2 is Run Import, you'll see step1 completed and you'll know step2 is running.

Alternatively, just insert all the steps into your own own audit table?

Answer 2

To answer directly to the question in the title: No. It's already specified in the documentation of sysjobhistory.

Contains information about the execution of scheduled jobs by SQL Server Agent. This table is stored in the msdb database.

Note: Data is updated only after the jobstep completes.

Regarding the other details, Peter's idea might work, though I don't understand why you need the step history id during the step execution itself, as you don't really know the error message, the step details, the duration, as it didn't finish the execution yet.

Answer 3

   SELECT *
   FROM MSDB.dbo.SysJobHistory sjh LEFT JOIN
        msdb.dbo.SysJobActivity activity
     ON activity.job_id = sjh.job_id

The SysJobActivity record is created once the job starts to run. However I have observed that the date/times in it didn't match up exactly to the SysJobHistory values or ranges in my environment. Not quite sure why.

Also, jobs that have run in the past don't always seem to have items in this SysJobActivity table. I puzzled over this. Because I was tempted to think that I could use it to find when things ran, etc. But then I found a whole bunch of rows in my SysJobHistory that had no matching SysJobActivity.

I think that run_requested_date, start_execution-date are filled in once the job starts running. last_executed_step_id I think is populated once it starts to run. I believe that step_order = 0 is always written to the SysJobHistory last. It may not write it right away as soon as it starts to write it, there is probably a lag.

Once the job is done running, then the row with step_order = 0 should be filled in.

It is trickey to look the SysJobHistory table and pair up all of the rows that are part of the same job run programmatically. But I will post on this somewhere else.

Here is a link to another stack exchange article where I posted. It is somewhat related to the one you are reading here now.