October 22, 2014 at 9:22 am
I'm looking for a way to get the SQL Server Agent Job ID from within a stored procedure that is being run from SQL Server Agent, either scheduled or ad-hoc.
I can see the information I want in sysjobsactivitiy but I can't see how to find the row that corresponds to the job being run from within the job.
Any suggestions?
Gerald Britton, Pluralsight courses
October 22, 2014 at 10:02 am
Maybe you could use the job name. Just offering some suggestions!
You know the job name because its listed in agent jobs in ssms explorer, so you could start by nabbing the job_id from sysjobs where job_name = 'your job name here', and then take that job_id and join to sysjobactivity and have a look. Not sure if that would do what you want though.
SELECT JOB_ID FROM MSDB..SYSJOBS JB
JOIN MSDB..SYSJOBACTIVITY JA ON
JB.JOB_ID = JA.JOB_ID
WHERE NAME = 'my_test_test'
AND START_EXECUTION_DATE IS NOT NULL ---<<< if you want more info like the current execution, otherwise you could just drop the execution_date stuff I guess.
AND STOP_EXECUTION_DATE IS NULL
ORDER BY NAME
October 22, 2014 at 10:24 am
The problem is the stored procedure can be called from more than one Agent job, so I cannot hard-code the jobname like that.
Gerald Britton, Pluralsight courses
October 22, 2014 at 10:52 am
g.britton (10/22/2014)
The problem is the stored procedure can be called from more than one Agent job, so I cannot hard-code the jobname like that.
Gotcha, maybe you can use master..sysprocesses from inside the job and chew up the 'program_name' a bit?
SELECT PROGRAM_NAME from MASTER..SYSPROCESSES WHERE @@SPID = SPID
From my test run, the 'program_name' column looks like it might have the job id inside it but there looks to be some byte swapping going on in the hex code. Like for instance, the trailing 12 hex digits look fine, the next 4 look fine, the next 4 have the bytes swapped, the next for again have the bytes swapped, and the next 8 hex digits are swapped around in completely reverse (byte) order.
LOL what a mess, never mind, I'm no help.
October 22, 2014 at 3:18 pm
You should be able to use the session_id to do the lookup. But since session_ids are reused, and sysjobactivity keeps history, if your proc is not currently running from within a job, you'll get a bogus job_id from some earlier process. But, if it's part of a currently executing job, this should give you the current job id.
SELECT TOP (1) ja.job_id
FROM msdb.dbo.sysjobactivity ja
WHERE
ja.session_id = @@SPID
ORDER BY ja.start_execution_date DESC
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
October 23, 2014 at 7:18 am
Interestingly, this does yield a jobid that I can use, even though it is not THE job id. e.g. using that query I get
72A6BEB9941ED146AD41CCEED345E01D
the actual job id (from sysjobs) is
B9BEA672-1E94-46D1-AD41-CCEED345E01D
Comparing the two, you can see that the last eight bytes match. I'm guessing that SQL Agent overwrites the first eight bytes with something of its own.
Gerald Britton, Pluralsight courses
October 23, 2014 at 7:56 am
g.britton (10/23/2014)
Interestingly, this does yield a jobid that I can use, even though it is not THE job id. e.g. using that query I get72A6BEB9941ED146AD41CCEED345E01D
the actual job id (from sysjobs) is
B9BEA672-1E94-46D1-AD41-CCEED345E01D
Comparing the two, you can see that the last eight bytes match. I'm guessing that SQL Agent overwrites the first eight bytes with something of its own.
If you look closely, they're reordered, each two characters represents a hex code for a byte, I broke down the reordering in the previous post where I surrendered LOL
72A6BEB9941ED146AD41CCEED345E01D
the actual job id (from sysjobs) is
B9BEA672-1E94-46D1-AD41-CCEED345E01D
--- put in the missing dashes
72A6BEB9-941E-D146-AD41-CCEED345E01D
B9BEA672-1E94-46D1-AD41-CCEED345E01D
xxxxxxxx-xxxx-xxxx <<<--- each respective group has its byte order reversed.
October 23, 2014 at 8:25 am
crazy! One wonders why....
Gerald Britton, Pluralsight courses
October 23, 2014 at 8:57 am
My theory is that intel is "little edian", which means that bytes stored in memory are stored with "increasing numeric significance with increasing memory addresses (or increasing time), known as little-endian" http://en.wikipedia.org/wiki/Endianness.
So one programmer decided to display that text as it sits in memory, and another programmer decided to display that text as it would be interpretted or used in a cpu register or in the case of the 8 character (4 byte) value, stored in an 'int', or in the case of the 4 character (2 byte), stored in a 'short'.
Neither noticed what the other programmer was doing.
but thats just a theory of course.
October 23, 2014 at 11:00 am
Ha! I like it!! Just likely enough to be true.
Gerald Britton, Pluralsight courses
October 23, 2014 at 11:09 am
And those same guys obviously did contract work for healthcare.gov! 😀
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
October 23, 2014 at 11:41 am
ScottPletcher (10/23/2014)
And those same guys obviously did contract work for healthcare.gov! 😀
Speaking of which, I wonder if the last bar in this chart is close to accurate?
http://www.informationisbeautiful.net/visualizations/million-lines-of-code/
They hedge their bets by including "apparent size" in the legend for that one.
edit: oops, sorry g.britton for trashing a thread again!
October 23, 2014 at 11:54 am
ScottPletcher (10/22/2014)
You should be able to use the session_id to do the lookup. But since session_ids are reused, and sysjobactivity keeps history, if your proc is not currently running from within a job, you'll get a bogus job_id from some earlier process. But, if it's part of a currently executing job, this should give you the current job id.
SELECT TOP (1) ja.job_id
FROM msdb.dbo.sysjobactivity ja
WHERE
ja.session_id = @@SPID
ORDER BY ja.start_execution_date DESC
Scott, correct me if I am wrong, but isn't session_id in sysjobactivity just an increasing value that is incremented each time SQL Agent is started?
When I look at syssessions, the rows correspond to restarts.
October 23, 2014 at 12:31 pm
arnipetursson (10/23/2014)
ScottPletcher (10/22/2014)
You should be able to use the session_id to do the lookup. But since session_ids are reused, and sysjobactivity keeps history, if your proc is not currently running from within a job, you'll get a bogus job_id from some earlier process. But, if it's part of a currently executing job, this should give you the current job id.
SELECT TOP (1) ja.job_id
FROM msdb.dbo.sysjobactivity ja
WHERE
ja.session_id = @@SPID
ORDER BY ja.start_execution_date DESC
Scott, correct me if I am wrong, but isn't session_id in sysjobactivity just an increasing value that is incremented each time SQL Agent is started?
When I look at syssessions, the rows correspond to restarts.
No. The session_id in sys.dm_exec_connections must be a differently-assigned session id, as obviously every connection is not made thru SQL Agent. Session_id is equivalent to spid ("server process id") in earlier versions of SQL. SQL will reuse those session_id values.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
October 23, 2014 at 1:36 pm
ScottPletcher (10/23/2014)
arnipetursson (10/23/2014)
ScottPletcher (10/22/2014)
You should be able to use the session_id to do the lookup. But since session_ids are reused, and sysjobactivity keeps history, if your proc is not currently running from within a job, you'll get a bogus job_id from some earlier process. But, if it's part of a currently executing job, this should give you the current job id.
SELECT TOP (1) ja.job_id
FROM msdb.dbo.sysjobactivity ja
WHERE
ja.session_id = @@SPID
ORDER BY ja.start_execution_date DESC
Scott, correct me if I am wrong, but isn't session_id in sysjobactivity just an increasing value that is incremented each time SQL Agent is started?
When I look at syssessions, the rows correspond to restarts.
No. The session_id in sys.dm_exec_connections must be a differently-assigned session id, as obviously every connection is not made thru SQL Agent. Session_id is equivalent to spid ("server process id") in earlier versions of SQL. SQL will reuse those session_id values.
Not trying to be a spoilsport, but I didn't get a session_id in that table that equaled the @@SPID of my test agent job.
From microsoft's page:
session_id is the "ID of the session stored in the syssessions table in the msdb database."
http://msdn.microsoft.com/en-us/library/ms190484(v=sql.100).aspx
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply