Below script returns five (5) columns result set as output. You can further enhance this query to accommodate your requirement.
- Job_Name: This is job’s registered name on the server’s instance.
- Last_Executed_Step_ID: This is the ID of the last executed step in the job. E.g. if a job has 4 steps, and currently it is running 3rd step, so last executed step will be 2 and it will be populated with 2 against Job’s 2nd step row, while all other steps rows will have 0.
- Step_ID: This query returns all steps included in the job along with their id, while 2nd column, returns id same as in this column.
- Step_Name: This reflects job step name that was given during job creation.
- Start_Execution_Date: This column returns execution date and time of this job. So you can get an idea when this started execution.
FROM msdb.dbo.sysjobs j WITH (NOLOCK)
INNER JOIN msdb.dbo.sysjobsteps s WITH (NOLOCK) ON j.job_id = s.job_id
LEFT OUTER JOIN msdb.dbo.sysjobactivity a WITH (NOLOCK) ON j.job_id=a.job_id
AND (s.step_id=a.last_executed_step_id OR a.last_executed_Step_id IS NULL)
AND run_requested_date>=CAST(CONVERT(VARCHAR,GETDATE(),101) AS DATETIME)
AND stop_execution_Date IS NULL
AND j.category_id IN (0,1,2,3,4,5,98,99)
AND j.job_id in
(SELECT job_id FROM msdb.dbo.sysjobactivity WITH (NOLOCK)
WHERE start_execution_date>= CAST(CONVERT(VARCHAR,GETDATE(),101) AS DATETIME)
AND stop_execution_date IS NULL )
ORDER BY 1,3