Introduction

This T-SQL script will demonstrate how to get job execution status in SQL Server. The script contains the execution status of each step of an agent job, and displays the contents of the T-SQL job and job-related information such as procedures, scheduling, the next execution time, etc. If your agent job contains ETL or Powershell or other, you can also view this job's execution status.

Scenarios  

As some people want to know how to get job execution status in SQL Server, this script will provide some help for us.

Script

You can use this script in this way:
1. Open SQL Server Management Studio (SSMS) and connect to SQL Server.

2. Select the specified database and create a "New Query", copy the code from GetJobExecutionStatus.sql, paste it and run the script.

After the script and example finishes running, we'll get the following figure:

Here are some code snippets for your reference.

SELECT
          h.[server]      AS ServerName,
          s.database_name AS DBNAME,
          s.subsystem     AS JobType,
          j.name          AS JobName,
          CASE WHEN j.enabled = 1 THEN 'Enable'
                     ELSE 'Disable'
                  END AS IsEnable,
          j.[Description],
       s.step_id       AS StepID,
       s.step_name     AS StepName,
          s.database_user_name,
          s.output_file_name AS 'Path',
          s.command       AS Command,
       msdb.dbo.agent_datetime(h.run_date, h.run_time) AS 'RunDatatime',
       CASE WHEN h.run_duration<=60  THEN h.run_duration
            WHEN h.run_duration>=100 THEN h.run_duration/100*60+(h.run_duration%100)
            END AS DurationSeconds,
       CASE h.run_status
                     WHEN 0 THEN 'Failed'
            WHEN 1 THEN 'Succeeded'
            WHEN 2 THEN 'Retry'
            WHEN 3 THEN 'Canceled'
            END AS RunStatus,

SELECT 
          h.[server]      AS ServerName, 
          s.database_name AS DBNAME, 
          s.subsystem     AS JobType, 
          j.name          AS JobName, 
          CASE WHEN j.enabled = 1 THEN 'Enable' 
                     ELSE 'Disable' 
                  END AS IsEnable, 
          j.[Description], 
       s.step_id       AS StepID, 
       s.step_name     AS StepName, 
          s.database_user_name, 
          s.output_file_name AS 'Path', 
          s.command       AS Command, 
       msdb.dbo.agent_datetime(h.run_date, h.run_time) AS 'RunDatatime', 
       CASE WHEN h.run_duration<=60  THEN h.run_duration 
            WHEN h.run_duration>=100 THEN h.run_duration/100*60+(h.run_duration%100) 
            END AS DurationSeconds, 
       CASE h.run_status 
                     WHEN 0 THEN 'Failed' 
            WHEN 1 THEN 'Succeeded' 
            WHEN 2 THEN 'Retry' 
            WHEN 3 THEN 'Canceled' 
            END AS RunStatus,

SQL Server 2008 or higher version

Microsoft All-In-One Script Framework is an automation script sample library for IT Professionals. The key value that All-In-One Script Framework is trying to deliver is Scenario-Focused Script Samples driven by IT Pros' real-world pains and needs. The team is monitoring all TechNet forums, IT Pros' support calls to Microsoft, and script requests submitted to TechNet Script Repository. We collect frequently asked IT scenarios, and create script samples to automate the tasks and save some time for IT Pros. The team of All-In-One Script Framework sincerely hope that these customer-driven automation script samples can help our IT community in this script-centric move.