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