Script: view information of currently running SQL Server Jobs

Below script returns five (5) columns result set as output. You can further enhance this query to accommodate your requirement.

1. Job_Name: This is job’s registered name on the server’s instance.
2. 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 3^rd step, so last executed step will be 2 and it will be populated with 2 against Job’s 2^nd step row, while all other steps rows will have 0.
3. Step_ID: This query returns all steps included in the job along with their id, while 2^nd column, returns id same as in this column.
4. Step_Name: This reflects job step name that was given during job creation.
5. Start_Execution_Date: This column returns execution date and time of this job. So you can get an idea when this started execution.

SELECT 
LTRIM(RTRIM(CONVERT(VARCHAR(100),j.name))) job_name

,ISNULL(a.last_executed_step_id,'') last_executed_step_id

,s.step_id

,RTRIM(CONVERT(VARCHAR(100),s.step_name))step_name

,a.start_execution_Date

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

WHERE j.enabled=1

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