SQL Server System Table Analysis (2)-job table

1. sysjobactivity table

Record the activity and status of the current SQL Server proxy job.

Job_id: Job ID
Run_requested_date: date and time when the job is requested to run
Run_requested_source: the requester requesting to run the job. 1 = source_scheduler, 4 = source_user
Start_execution_date: date and time when the job is scheduled to run
Job_history_id: used to identify rows in the sysjobhistory table.
Next_scheduled_run_date: the date and time of the next job to be run.

2. sysjobhistory table

Contains information about the scheduled job executed by the SQL Server proxy.
It is easy to understand, that is, to record the history of job execution. This table is useful for viewing the job execution time, status, and completion information.

Job_id: Job ID
Step_id: The Step ID in the job. A job can contain more than one step.
Step_name: Step name
Message: job execution information, which records whether the job is successfully executed. If it fails, it contains failure information, which is very useful.
Run_status: Execution status of the job, 0 = failed, 1 = successful, 2 = retry, 3-canceled
Run_data: the date on which the job or step starts to run.
Run_time: the start time of the job fire step.
Run_duration: the time it takes to execute a job or step. The unit is milliseconds.

3. sysjobs table

Information about each scheduled job executed by the SQL Server proxy

Job_id: Unique ID of a job
Name: Job name
Enabled: whether the job is enabled
Description: job description
Start_step_id: the actual step ID of the job to be executed
Category_id: ID of the job category
Delete_level: indicates under which conditions should a job be deleted when the job is completed 0 = never, 1 = success, 2 = failure, 3 = completion
Data_created: the job creation date.
Date_modified: Date on which the job was last modified
Version_number: Job version

4. sysjobservers table

Msdn: stores the associations or relationships between a specific job and one or more target servers.
In my opinion, it is used to store the details of the last job execution. This table is convenient for observing the latest information about job execution.

Job_id: Job ID
Last_run_outcome: Result of the last job run. 0 = successful, 1-failed, 3-canceled
Last_run_message: Message associated with the last_run_outcome Column
Last_run_date: date on which the last job was run
Last_run_time: the time when the last job was run.
Last_run_duration: duration of last job running

5. sysjobschedules table

Information about the next execution time of a job

Schedule_id: Plan ID
Job_id: Job ID
Next_run_date: the next date of the job to be run
Next_run_time: the time when the job is scheduled to run.

6. sysjobsteps table

Contains information about each step in the job to be executed by the SQL Server proxy.

Job_id: Job ID
Step_id: Step ID in the job
Step_name: job step name
Command: the command to be executed by subsystem. It is important to directly obtain the SQL command to be executed in this step.
Last_run_duration: duration of the last running of this step

7.Sysjobstepslogs table

Job step logs that contain all SQL Server proxy job steps. These job steps are configured to write job step output to the table.

Log_id: ID of the job step log
Log: content of the job step log
Date_created: date and time when the job step log is created
Log_size: size of the job step log

In practice, get the latest job execution information:

 Select     Top     150  A. run_date, A. run_time, B. Name, step_id, step_name, A. Message, A. run_status, A. run_duration
  From MSDB. DBO. sysjobhistory A, MSDB. DBO. sysjobs B
  Where  A. job_id  =  B. job_id  And  Name  Not     In  (  '  Job_exclude  '  )  And  A. step_id >  0  
  Order     By  Run_date  Desc 

RelatedArticle:
SQL Server System Table analysis (1)-Backup table
SQL Server monitors job execution