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