How does SQL Server accurately obtain the Job Scheduling time?

The following are three methods to get the job scheduling time:

First:

 1   Declare  
 2       @ Jobid   Varchar ( 50 ),
 3       @ Newjobid   Varchar ( 50 )
 4   Select   @ Jobid   =  Replace (Job_id, '  -  ' , '' )
 5   From MSDB. DBO. sysjobs Where Name =   '  Create_snapshotjob  ' 
 6   Select   @ Jobid 
7   Set   @ Newjobid   =   Substring ( @ Jobid , 7 , 2 ) +  Substring ( @ Jobid , 5 , 2 ) + Substring ( @ Jobid , 3 , 2 ) +  Substring ( @ Jobid , 1 , 2 )
 8       +  Substring ( @ Jobid ,11 , 2 ) +  Substring ( @ Jobid , 9 , 2 ) +  Substring ( @ Jobid , 15 , 2 ) + Substring ( @ Jobid , 13 , 2 ) +  Substring ( @ Jobid , 17 , 50 )
 9  
 10   Select A. * , B.* 
 11   From SYS. dm_exec_sessions
 12   Inner   Join SYS. dm_exec_requests B On B. session_id = A. session_id
 13   Where Program_name Like   '  SQLAgent-tsql jobstep (job 0x  ' +  @ Newjobid  +  '  %  ' 

Second:

 
ExecMSDB. DBO. sp_help_job
 

Third:

 
ExecMaster. DBO. xp_sqlagent_enum_jobs1,''
 

The first and second methods should be obtained within the SQL Server service based on the execution status. The first method is to obtain the job Process status, and the second method is unknown. However, by observing the second and first, the performance is basically the same.

 

 

We can see that three scripts are captured during job execution.

The first method can capture the process information of a job. The job is being executed. Here, a create database process is executed.

The second method shows that the current job's current_execution_status is 1 and the execution step is displayed at the same time.

The third method is the same as the second method. If running is set to 1, the execution status is set to "current step ".

This shows that all three methods can capture the execution of the job. In this case, which method will be used?

 

The figure above is captured at the end of job execution. We can see that the job is still being executed, but the first and second methods show that the job has been completed. In the third method, we can see that the job is still being executed, but the current step is 0, indicating that no step is being executed.

 

From the above analysis, we can conclude that the first and second methods should be able to obtain the execution steps of the job but cannot obtain the current accurate status of the entire job. The third method can obtain every step of the job and all the conditions of the entire job. You can select a method based on your needs.