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) A socialist is someone who will give you the shirt off *someone else's* back.