Can a SQL Server Agent Job know its own Instance Id

[Tony O]

Is there any way to write a piece of tsql running within an sql server agent
job to get that running job's instance_id (instance_id from sysjobhistory)
and say, write that instance id to a custom log table?

[Erland Sommarskog]

Post by Tony O
Is there any way to write a piece of tsql running within an sql server
agent job to get that running job's instance_id (instance_id from
sysjobhistory) and say, write that instance id to a custom log table?

app_name() contains an id, although you will need to do some parsing to
extract it.

[Tony O]

Unfortunately this gives us only the Job Id and the Step Id. Is there any
way to get the Instance Id?

Post by Tony O
Is there any way to write a piece of tsql running within an sql server
agent job to get that running job's instance_id (instance_id from
sysjobhistory) and say, write that instance id to a custom log table?

app_name() contains an id, although you will need to do some parsing to
extract it.

[Erland Sommarskog]

Post by Tony O
Unfortunately this gives us only the Job Id and the Step Id. Is there any
way to get the Instance Id?

That's what I get for not actually looking at the table defintion to
see what you are looking for.

Then gain, if you have job_id and step_id and you know that run_status is =
4, you could use sys.dm_exec_connections.connect_time to map to run_date and
run_time in sysjobshistory. At least as long as not two instance of the job
are not starting at the same time. You probably need some leeway when you do
this mapping since the table only has precision by whole seconds. And beware
that the format for date and time is funky...

[rpresser]

Even sysjobhistory.instance_id may not be what you want. See this thread, which claims it identifies the job step, not the job:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114687&whichpage=1