how to know status of currently running jobs
EXEC msdb.dbo.sp_help_job @Job_name = 'Your Job Name'
check field execution_status
0 - Returns only those jobs that are not idle or suspended.
1 - Executing.
2 - Waiting for thread.
3 - Between retries.
4 - Idle.
5 - Suspended.
7 - Performing completion actions.
If you need the result of execution, check the field last_run_outcome
0 = Failed
1 = Succeeded
3 = Canceled
5 = Unknown
https://msdn.microsoft.com/en-us/library/ms186722.aspx
Given a job (I assume you know its name) you can use:
EXEC msdb.dbo.sp_help_job @Job_name = 'Your Job Name'
as suggested in MSDN Job Help Procedure. It returns a lot of informations about the job (owner, server, status and so on).
I found a better answer by Kenneth Fisher. The following query returns only currently running jobs:
SELECT
ja.job_id,
j.name AS job_name,
ja.start_execution_date,
ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
Js.step_name
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
ON ja.job_id = js.job_id
AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE
ja.session_id = (
SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC
)
AND start_execution_date is not null
AND stop_execution_date is null;
You can get more information about a job by adding more columns from msdb.dbo.sysjobactivity
table in select clause.
It looks like you can use msdb.dbo.sysjobactivity
, checking for a record with a non-null start_execution_date and a null stop_execution_date, meaning the job was started, but has not yet completed.
This would give you currently running jobs:
SELECT sj.name
, sja.*
FROM msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
WHERE sja.start_execution_date IS NOT NULL
AND sja.stop_execution_date IS NULL