Query SQL Server Agent Jobs, Job Steps, History and Schedule System Tables
I need to know more about how SQL Server Agent Jobs and the supporting information stored in the MSDB database.
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_Success 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.
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 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.
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
- Check out these tips:
Last Updated: 2019-07-22
About the author
View all my tips