Query SQL Server Agent Jobs, Steps, History and System Tables

By: Eric Blinn | Updated: 2022-04-07 | Comments (4) | Related: > SQL Server Agent

Problem

I need to know more about how SQL Server Agent Jobs and the supporting information stored in the MSDB database.

Solution

MSDB is the home of the SQL Server Agent data. In it, one can find the jobs, job steps, schedules, operators, and execution history. All of these tables can be queried directly as shown in the examples below. Also, all of these queries should be run in the MSDB database.

SQL Agent Jobs and Job Steps Tables and Queries

Each SQL Server Agent Job is stored as a row in the table msdb.dbo.sysjobs. The primary key of this table is a guid called job_id. Each step in a job is found in the table msdb.dbo.sysjobsteps and they are joined using the job_id column.

Consider this test job with 2 steps, 1 T-SQL and 1 PowerShell.

This job information can be viewed using this query that joins sysjobs and sysjobsteps.

SELECT 
  sj.name JobName
, sj.enabled
, sj.start_step_id
, sjs.step_id
, sjs.step_name
, sjs.subsystem
, sjs.command
, CASE on_success_action
    WHEN 1 THEN 'Quit with success'
    WHEN 2 THEN 'Quit with failure'
    WHEN 3 THEN 'Go to next step'
    WHEN 4 THEN 'Go to step ' + CAST(on_success_step_id AS VARCHAR(3))
  END On_Success
, CASE on_fail_action
    WHEN 1 THEN 'Quit with success'
    WHEN 2 THEN 'Quit with failure'
    WHEN 3 THEN 'Go to next step'
    WHEN 4 THEN 'Go to step ' + CAST(on_fail_step_id AS VARCHAR(3))
  END On_Failure
FROM dbo.sysjobs sj
  INNER JOIN dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
WHERE sj.name = 'MSSQLTips Demo Job'

SQL Agent Jobs Schedules and Schedule Assignment Tables and Query

Each schedule is stored in a table called msdb.dbo.sysschedules. The information in sysschedules is not very human readable. This query will help make it much more readable. It depends on a new scalar function which can be applied to MSDB.

CREATE FUNCTION dbo.DaysOfWeekDecoder (@Freq_Interval INT) RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @RetVal VARCHAR(100);
IF @Freq_Interval &  1 =  1 SET @RetVal = 'Sunday';
IF @Freq_Interval &  2 =  2 SET @RetVal = CASE WHEN @RetVal IS NULL THEN '' ELSE @RetVal + ', ' END + 'Monday';
IF @Freq_Interval &  4 =  4 SET @RetVal = CASE WHEN @RetVal IS NULL THEN '' ELSE @RetVal + ', ' END + 'Tuesday';
IF @Freq_Interval &  8 =  8 SET @RetVal = CASE WHEN @RetVal IS NULL THEN '' ELSE @RetVal + ', ' END + 'Wednesday';
IF @Freq_Interval & 16 = 16 SET @RetVal = CASE WHEN @RetVal IS NULL THEN '' ELSE @RetVal + ', ' END + 'Thursday';
IF @Freq_Interval & 32 = 32 SET @RetVal = CASE WHEN @RetVal IS NULL THEN '' ELSE @RetVal + ', ' END + 'Friday';
IF @Freq_Interval & 64 = 64 SET @RetVal = CASE WHEN @RetVal IS NULL THEN '' ELSE @RetVal + ', ' END + 'Saturday';
RETURN @RetVal;                 
END

And then the query.

SELECT 
  schedule_id
, name
, enabled
, CASE freq_type
    WHEN 1   THEN 'One time only'
    WHEN 4   THEN 'Daily'
    WHEN 8   THEN 'Weekly'
    WHEN 16  THEN 'Monthly'
    WHEN 32  THEN 'Monthly'
    WHEN 64  THEN 'Runs when the SQL Server Agent service starts'
    WHEN 128 THEN 'Runs when the computer is idle'
  END AS FrequencyType
, CASE WHEN freq_type = 32 AND freq_relative_interval <> 0 THEN 
    CASE freq_relative_interval 
      WHEN 1  THEN 'First'
      WHEN 2  THEN 'Second'
      WHEN 4  THEN 'Third'
      WHEN 8  THEN 'Fourth'
      WHEN 16 THEN 'Last'
    END
    ELSE 'UNUSED' 
  END Interval
, CASE freq_type
    WHEN 1   THEN 'UNUSED'
    WHEN 4   THEN 'Every ' + CAST(freq_interval AS VARCHAR(3)) + ' Day(s)'
    WHEN 8   THEN dbo.DaysOfWeekDecoder(freq_interval)
    WHEN 16  THEN 'On day ' + CAST(freq_interval AS VARCHAR(3)) + ' of the month.'
    WHEN 32  THEN CASE freq_interval
                    WHEN 1  THEN 'Sunday'
                    WHEN 2  THEN 'Monday'
                    WHEN 3  THEN 'Tuesday'
                    WHEN 4  THEN 'Wednesday'
                    WHEN 5  THEN 'Thursday'
                    WHEN 6  THEN 'Friday'
                    WHEN 7  THEN 'Saturday'
                    WHEN 8  THEN 'Day'
                    WHEN 9  THEN 'Weekday'
                    WHEN 10 THEN 'Weekend day'
                  END
    WHEN 64  THEN 'UNUSED'
    WHEN 128 THEN 'UNUSED'
  END
, CASE WHEN freq_subday_interval <> 0 THEN 
    CASE freq_subday_type
      WHEN 1 THEN 'At ' + CAST(freq_subday_interval AS VARCHAR(3))
      WHEN 2 THEN 'Repeat every ' + CAST(freq_subday_interval  AS VARCHAR(3)) + ' Seconds'
      WHEN 4 THEN 'Repeat every ' + CAST(freq_subday_interval  AS VARCHAR(3)) + ' Minutes'
      WHEN 8 THEN 'Repeat every ' + CAST(freq_subday_interval  AS VARCHAR(3)) + ' Hours'
    END 
    ELSE 'UNUSED'
  END DailyFrequency
, CASE 
    WHEN freq_type = 8 THEN 'Repeat every ' + CAST(freq_recurrence_factor AS VARCHAR(3)) + ' week(s).'
    WHEN freq_type IN (16,32)      THEN 'Repeat every ' + CAST(freq_recurrence_factor AS VARCHAR(3)) + ' month(s).'
    ELSE 'UNUSED'
  END Interval2
, STUFF(STUFF(RIGHT('00000' + CAST(active_start_time AS VARCHAR(6)),6),3,0,':'),6,0,':')StartTime
, STUFF(STUFF(RIGHT('00000' + CAST(active_end_time AS VARCHAR(6)),6),3,0,':'),6,0,':') EndTime
FROM dbo.sysschedules

A schedule may be shared among many jobs or may have no jobs that use it. A job may any number of schedules attached to it –or it could have no schedules at all. To support these possibilities there is another table called msdb.dbo.sysjobschedules. Every row in this table marries one job with one schedule. It is commonly joined to both sysjobs and sysschedules.

SELECT schedule_id, job_id FROM dbo.sysjobschedules

SQL Agent Job History Tables and Query

The main job history table is msdb.dbo.sysjobhistory. Every time a SQL Server Agent job is executed there is a row placed in this table for each step of the job that executes. Each history row is identified by the job_id and step_id columns from sysjobsteps. At the end, one additional row is added to the table to report the overall job completion status and is given the step_id value of 0.

This is a SSMS job history screenshot from the demo job above. The job has 2 steps, numbered 1 and 2. The top row is for the entire job and will have a step_id of 0.

This is a SSMS job history screenshot from the demo job above. It show 3 rows. One each for steps 1 and 2. A third row has no step ID value in the screenshot. This is the 0 row in the table.

The following query would be used to query most of this same data. There are a few columns that have strange data types. Run_Date (20190527), run_time (221227), and run_duration (1) are all stored as integers. The run_date and run_time can be combined using the pre-installed dbo.agent_datetime scalar function. The run_duration can be converted to a more readable time using a series of STUFF functions.

SELECT 
  sj.name JobName
, sjh.step_id
, ISNULL(sjs.step_name, 'Job Status') StepName
, dbo.agent_datetime(sjh.run_date, sjh.run_time) RunDateAndTime
, STUFF(STUFF(RIGHT('00000' + CAST(run_duration AS VARCHAR(6)),6),3,0,':'),6,0,':') 
, CASE sjh.run_status
    WHEN 0 THEN 'Failed'
    WHEN 1 THEN 'Succeeded'
    WHEN 2 THEN 'Retry'
    WHEN 3 THEN 'Canceled'
    WHEN 4 THEN 'In Progress'
  END RunStatus
, sjh.message
FROM dbo.sysjobs sj
  INNER JOIN dbo.sysjobhistory sjh ON sj.job_id = sjh.job_id
  LEFT OUTER JOIN dbo.sysjobsteps sjs ON sjh.job_id = sjs.job_id AND sjh.step_id = sjs.step_id  
WHERE sj.name = 'MSSQLTips Demo Job'

The message column in the sysjobhistory table is limited to 4000 characters (1000 on older versions of SQL Server) of output for each step and the job completion row. Any data beyond the character limit is truncated. This truncation can be a problem when a job step has lot of output and the actual error text extends into the truncated data. This problem can be mitigated by using the enhanced history stored in msdb.dbo.sysjobstepslog. That table stores the message text in a MAX column and therefore has no practical limit on the text it can store.

The enhanced history is set on every step of a job individually and can be enabled by checking the "Log to table" box on the Advanced pane of the job step properties as seen in the screenshot below.

This screenshot shows the "Log to table" checkbox on the properties of the job step.

This query will show that column. For more details there is an entire tip dedicated to this topic.

SELECT 
  sj.name JobName
, sjs.step_id
, sjs.step_name
, sjsl.log
FROM dbo.sysjobs sj
  LEFT OUTER JOIN dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
  LEFT OUTER JOIN dbo.sysjobstepslogs sjsl ON sjs.step_uid = sjsl.step_uid
WHERE sj.name = 'MSSQLTips Demo Job'

SQL Agent Job History Housekeeping

The settings in this screenshot should look pretty familiar to most DBAs. The row counts are referring to the number of rows kept in sysjobhistory. If this isn’t granular enough there is an entire tip dedicated to smarter housekeeping routines.

This screenshot shows the SQL Server Agent properties window with the History page up. This page allows the user to set a limit on rows kept for the job history log.

SQL Agent Operators Table and Query

The SQL Server Agent supports sending emails or network messages to people that support the SQL Server when Agent jobs complete. This is usually reserved for when a job fails. These accounts are tracked in the operators list and can be found in the msdb.dbo.sysoperators table.

This query shows how to join sysjobs to sysoperators to find the operator that is emailed when a job finishes.

SELECT 
  sj.name
, so.name OperatorName
, so.email_address
FROM dbo.sysjobs sj
  LEFT OUTER JOIN dbo.sysoperators so ON sj.notify_email_operator_id = so.id

Next Steps

Check out these tips:

About the author

Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-04-07

Query SQL Server Agent Jobs, Job Steps, History and Schedule System Tables