Skip to content

Latest commit

 

History

History
95 lines (84 loc) · 5.21 KB

dbo-sysjobhistory-transact-sql.md

File metadata and controls

95 lines (84 loc) · 5.21 KB
Raw
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
dbo.sysjobhistory (Transact-SQL)
Contains information about the execution of scheduled jobs by the SQL Server Agent.
VanMSFT
vanto
randolphwest
03/27/2024
sql
system-objects
reference
dbo.sysjobhistory_TSQL
dbo.sysjobhistory
sysjobhistory
sysjobhistory_TSQL
sysjobhistory system table
TSQL

dbo.sysjobhistory (Transact-SQL)

[!INCLUDE SQL Server SQL MI]

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 [!INCLUDE ssNoVersion] error message returned if the job failed.
sql_severity int Severity of any [!INCLUDE ssNoVersion] error.
message nvarchar(4000) Text, if any, of a [!INCLUDE ssNoVersion] 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, 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.

Examples

The following [!INCLUDE tsql] 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 [!INCLUDE ssManStudioFull] 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

Related content