From time to time, I need to check (in my script) which jobs are running at the moment.So far, I have used the following three ways:
1. exec msdb..sp_help_job @execution_status = 1
2. exec master..xp_sqlagent_enum_jobs 1, 'sa' -- checking the column [running], if it is 1, it means the job is running
3. my "invention":-)
select j.* from msdb..sysjobs j
inner join sys.dm_exec_sessions s
on master.sys.fn_varbintohexstr(j.job_id) = substring(s.program_name, 30, 34)
Please comment if you have any other good ways ..