title: SQL Querying Job Activity Monitor for Status
body: <p>I'm trying to write a query to find the current status of a SQL Agent Job.  This field exists under the "Status" column in Job Activity Monitor.  See screenshot below:</p>
  
  <p><a href="https://i.stack.imgur.com/bBoOP.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bBoOP.png" alt="enter image description here"></a></p>
  
  <p>I'm using the queries found on <a href="https://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/" rel="nofollow noreferrer">this site</a> (query also below), which are great but the status it returns is the status of a job that has already been run.</p>
  
  <p>Does anyone know of a way to query the current status vs. the status the job ended at?  In the example above I would want to return "Executing 1(Run Query)".  "Run Query" is the name of step 1 of the job.</p>
  
  <pre><code>SELECT 
      [sJOB].[job_id] AS [JobID]
      , [sJOB].[name] AS [JobName]
      , CASE 
          WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
          ELSE CAST(
                  CAST([sJOBH].[run_date] AS CHAR(8))
                  + ' ' 
                  + STUFF(
                      STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)),  6)
                          , 3, 0, ':')
                      , 6, 0, ':')
                  AS DATETIME)
        END AS [LastRunDateTime]
      , CASE [sJOBH].[run_status]
          WHEN 0 THEN 'Failed'
          WHEN 1 THEN 'Succeeded'
          WHEN 2 THEN 'Retry'
          WHEN 3 THEN 'Canceled'
          WHEN 4 THEN 'Running' -- In Progress
        END AS [LastRunStatus]
      , STUFF(
              STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)),  6)
                  , 3, 0, ':')
              , 6, 0, ':') 
          AS [LastRunDuration (HH:MM:SS)]
      , [sJOBH].[message] AS [LastRunStatusMessage]
      , CASE [sJOBSCH].[NextRunDate]
          WHEN 0 THEN NULL
          ELSE CAST(
                  CAST([sJOBSCH].[NextRunDate] AS CHAR(8))
                  + ' ' 
                  + STUFF(
                      STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),  6)
                          , 3, 0, ':')
                      , 6, 0, ':')
                  AS DATETIME)
        END AS [NextRunDateTime]
  FROM 
      [msdb].[dbo].[sysjobs] (NOLOCK) AS [sJOB]
      LEFT JOIN (
                  SELECT
                      [job_id]
                      , MIN([next_run_date]) AS [NextRunDate]
                      , MIN([next_run_time]) AS [NextRunTime]
                  FROM [msdb].[dbo].[sysjobschedules] (NOLOCK)
                  GROUP BY [job_id]
              ) AS [sJOBSCH]
          ON [sJOB].[job_id] = [sJOBSCH].[job_id]
      LEFT JOIN (
                  SELECT 
                      [job_id]
                      , [run_date]
                      , [run_time]
                      , [run_status]
                      , [run_duration]
                      , [message]
                      , ROW_NUMBER() OVER (
                                              PARTITION BY [job_id] 
                                              ORDER BY [run_date] DESC, [run_time] DESC
                        ) AS RowNumber
                  FROM [msdb].[dbo].[sysjobhistory] (NOLOCK)
                  WHERE [step_id] = 0
              ) AS [sJOBH]
          ON [sJOB].[job_id] = [sJOBH].[job_id]
          AND [sJOBH].[RowNumber] = 1
  WHERE [sJOB].[job_id] = '527BA180-A5D9-4492-98F0-705889EBCFC4'
  ORDER BY [JobName]
  </code></pre>