Find the Session ID of an executing SQL Agent job

  • Article

On a busy production server I wanted to check when a job started running , I tried to look at the sysjob history table in the MSDB

 

However there are no entries for inflight jobs (meaning no entries for job that haven't failed or pass)

The sysjobschedules table from msdb will only show you failed or successful runs

 

While there are scripts available online to do this , you can use the below DMV's as well

 

select * from sys.jobs

-->Copy the job ID of your job name

select PROGRAM_NAME , * from sysprocesses where spid> 50

 

The result will be something like:

SQLAgent - TSQL JobStep (Job xx23453AB7EC97864084xxxxxF72B8CC9E : Step 1)

-->Make sure the program_name matches the Job ID you are looking for
Get the spid from here

 

--> You can now use it in below DMV's to check for blockings or waittypes

select * from sys.dm_exec_requests where session_id = 102
select last_batch, * from sysprocesses where spid = 102