begin declare @jobstatus table(job_id uniqueidentifier, last_run_date int, last_run_time int, next_run_date int, next_run_time int,next_run_schedule_id int, requested_to_run int, request_source int, request_source_id varchar(100), running int, current_step int, current_retry_attempt int, state int) insert into @jobstatus exec master.dbo.xp_sqlagent_enum_jobs 1,garbage begin select distinct case when state=1 then 'job is executing' when state=2 then 'waiting for thread to complete' when state=3 then 'between retries' when state=4 then 'job is idle' when state=5 then 'job is suspended' when state=7 then 'performing completion actions' end as state,sj.name, case when ej.running=1 then st.step_id else 0 end as currentstepid, case when ej.running=1 then st.step_name else 'not executing' end as currentstepname, st.command, ej.request_source_id from @jobstatus ej join msdb..sysjobs sj on sj.job_id=ej.job_id join msdb..sysjobsteps st on st.job_id=ej.job_id and (st.step_id=ej.current_step or ej.current_step=0) where ej.running+1>1 end end