dbo.sysjobhistory (Transact-SQL)
Applies to: SQL Server Azure SQL Managed Instance
Contains information about the execution of scheduled jobs by the SQL Server Agent.
Note
In most cases, the data is updated only after the job step completes, and the table typically contains no records for job steps that are currently in progress. In some cases, underlying processes do provide information about in progress job steps.
This table is stored in the msdb
database.
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 SQL Server error message returned if the job failed. |
sql_severity |
int | Severity of any SQL Server error. |
message |
nvarchar(4000) | Text, if any, of a SQL Server error. |
run_status |
int | Status of the job execution:0 = Failed1 = Succeeded2 = Retry3 = Canceled4 = In Progress |
run_date |
int | Date the job or step started execution, in yyyyMMdd format. For an In Progress history, this value is the date and time the history was written. |
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 for time periods up to 24 hours. Find code to translate longer run durations in the example. |
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. |
The following Transact-SQL query converts the run_date
and run_time
columns into a datetime column named LastRunStartDateTime
. The run_duration
column is converted into an int column named LastRunDurationSeconds
. These two columns are then used to calculate the LastRunFinishDateTime
. The run_duration
column is also converted into a more user-friendly format. You can run the script in SQL Server Management Studio or Azure Data Studio.
SET NOCOUNT ON;
SELECT sj.name AS Name,
sh.step_name AS StepName,
shp.LastRunStartDateTime,
DATEADD(SECOND, shp.LastRunDurationSeconds, shp.LastRunStartDateTime) AS LastRunFinishDateTime,
shp.LastRunDurationSeconds,
CASE
WHEN sh.run_duration > 235959
THEN CAST((CAST(LEFT(CAST(sh.run_duration AS VARCHAR),
LEN(CAST(sh.run_duration AS VARCHAR)) - 4) AS INT) / 24) AS VARCHAR)
+ '.' + RIGHT('00' + CAST(CAST(LEFT(CAST(sh.run_duration AS VARCHAR),
LEN(CAST(sh.run_duration AS VARCHAR)) - 4) AS INT) % 24 AS VARCHAR), 2)
+ ':' + STUFF(CAST(RIGHT(CAST(sh.run_duration AS VARCHAR), 4) AS VARCHAR(6)), 3, 0, ':')
ELSE STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(sh.run_duration AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
END AS [LastRunDuration (d.HH:MM:SS)],
DATEADD(SECOND, shp.LastRunDurationSeconds, shp.LastRunStartDateTime) AS LastRunFinishDateTime
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobhistory sh ON sj.job_id = sh.job_id
CROSS APPLY (SELECT DATETIMEFROMPARTS(sh.run_date / 10000, -- years
sh.run_date % 10000 / 100, -- months
sh.run_date % 100, -- days
sh.run_time / 10000, -- hours
sh.run_time % 10000 / 100, -- minutes
sh.run_time % 100, -- seconds
0 -- milliseconds
) AS LastRunStartDateTime,
(sh.run_duration / 10000) * 3600 -- convert hours to seconds, can be greater than 24
+ ((sh.run_duration % 10000) / 100) * 60 -- convert minutes to seconds
+ (sh.run_duration % 100) AS LastRunDurationSeconds
) AS shp;
GO