sysjobhistory

Contains information about the execution of scheduled jobs.

Column name	Data type	Description
instance_id	int	Unique identifier for the row.
job_id	uniqueidentifier	Job ID.
step_id	int	ID of the step in the job.
step_name	sysname	Name of the step.
sql_message_id	int	ID of any error message returned if the job failed.
sql_severity	int	Severity of any engine error.
message	nvarchar(4000)	Text, if any, of the engine error.
run_status	int	Status of the job execution: 0 = Failed, 1 = Succeeded, 2 = Retry, 3 = Canceled, 4 = In Progress.
run_date	int	Date the job or step started execution.
run_time	int	Time the job or step started in HHMMSS format.
run_duration	int	Elapsed time in the execution of the job or step in HHMMSS format.
operator_id_emailed	int	ID of the operator notified when the job completed.
operator_id_netsent	int	ID of the operator notified by a message when the job completed.
operator_id_paged	int	ID of the operator notified by pager when the job completed.
retries_attempted	int	Number of retry attempts for the job or step.
server	sysname	Name of the server where the job was executed.

Example

The following SQL query will convert the run_time and run_duration columns into a more user friendly format.

SELECT sj.name
     , sh.run_date
     , sh.step_name
     , STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(sh.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') 'run_time'
     , STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(sh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'run_duration (DD:HH:MM:SS)'
  FROM sysjobs sj
  JOIN sysjobhistory sh
    ON sj.job_id = sh.job_id