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

Problem

I need to know more about how SQL Server Agent Job history and the supporting queries. I need to access the applicable data stored in the MSDB database system tables. What T-SQL queries can I issue to access this data to improve decision making?

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 and run in the MSDB, as shown in the examples below.

SQL Agent Jobs and Job Steps Tables and Queries

The msdb.dbo.sysjobs table stores each SQL Server Agent Job as a row with a guid primary key on the job_id column. The table msdb.dbo.sysjobsteps holds each step in a job and joined using the job_id column.

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

View job information with this query joining 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

The msdb.dbo.sysschedules table stores each schedule. The information in sysschedules is not very human-readable. This query will help make it much more readable, based on a scalar function.

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

Job schedules may be shared among many jobs or may have no jobs that use it. A job may have any number of schedules attached to it or it could have no schedules at all. To support these possibilities, query another table called msdb.dbo.sysjobschedules. Every row in this table marries one job with one schedule, 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. Each step of the job execution places a row in this table. The job_id and step_id columns from sysjobsteps identify each history row. One additional row reports the overall job completion with a step_id value of 0.

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

The following query returns most of the 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. Use the pre-installed dbo.agent_datetime scalar function to combine the run_date and run_time. A series of STUFF functions can convert the run_duration to a more readable time.

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 stores up to 4000 characters (1000 on older versions of SQL Server) of output for each step and the job completion row.  Data truncation happens if it goes beyond the character limit.  This truncation can be a problem when a job step has a lot of output, and the actual error text extends into the truncated data.  Use the enhanced history stored in msdb.dbo.sysjobstepslog to mitigate the problem.  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 query will show that column.  For more details, check out 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.

SQL Agent Operators Table and Query

The SQL Server Agent supports sending emails or network messages to people who support the SQL Server when Agent jobs are completed.  This is usually reserved for when a job fails.  These accounts are tracked in the operators list 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:
  • Get started with the SQL Agent
  • Storing longer Agent messages
  • Managing Agent History
  • Managing Agent History More Effectively