SQL Server Agent Jobs, how do I query the status?

- July 07, 2010

If you need to find the current status of the SQL Server Agent Jobs the following query is usefull

select name, description,start_execution_date,  stop_execution_date, 
  msdb.dbo.sysjobs.Job_ID, 
  job_status = CASE 
  WHEN start_execution_date IS NULL AND stop_execution_date IS NULL THEN 0 
  WHEN start_execution_date IS NOT NULL AND stop_execution_date IS NULL THEN 1   
  WHEN start_execution_date IS NOT NULL AND stop_execution_date IS NOT NULL THEN 2   
  ELSE 3
  END
FROM msdb.dbo.sysjobactivity JA inner join 
msdb.dbo.sysjobs 
ON JA.Job_ID = msdb.dbo.sysjobs.Job_ID