dbo.sysjobhistory (Transact-SQL)

 

Updated: August 3, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Contains information about the execution of scheduled jobs by SQL Server Agent. This table is stored in the msdb database.

NOTE: Data is updated only after the jobstep completes.

Column nameData typeDescription
instance_idintUnique identifier for the row.
job_iduniqueidentifierJob ID.
step_idintID of the step in the job.
step_namesysnameName of the step.
sql_message_idintID of any SQL Server error message returned if the job failed.
sql_severityintSeverity of any SQL Server error.
messagenvarchar(4000)Text, if any, of a SQL Server error.
run_statusintStatus of the job execution:

 0 = Failed

 1 = Succeeded

 2 = Retry

 3 = Canceled
run_dateintDate the job or step started execution. For an In Progress history, this is the date/time the history was written.
run_timeintTime the job or step started.
run_durationintElapsed time in the execution of the job or step in HHMMSS format.
operator_id_emailedintID of the operator notified when the job completed.
operator_id_netsentintID of the operator notified by a message when the job completed.
operator_id_pagedintID of the operator notified by pager when the job completed.
retries_attemptedintNumber of retry attempts for the job or step.
serversysnameName of the server where the job was executed.

The following Transact-SQL query will convert the run_time and run_duration columns into a more user friendly format. Execute the script in SQL Server Management Studio.

SET NOCOUNT ON;

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 msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobhistory sh
ON sj.job_id = sh.job_id

Community Additions

ADD
Show: