SQL SERVER – T-SQL Script to Check SQL Server Job History

Yesterday my long time customer from New Zealand asked me during our regular weekly meet of Comprehensive Database Performance Health Check if I know a T-SQL Script to Check SQL Server Job History.

SQL SERVER - T-SQL Script to Check SQL Server Job History jobhistory

Absolutely, here is the simple script, I use to check all the SQL Server Agent Job History. Please note that while writing this script, I have taken help of the function agent_datetime which I have blogged earlier a few years ago.

Let us see the T-SQL Script:

SELECT jobs.name AS 'JobName',
msdb.dbo.agent_datetime(run_date, run_time) AS 'Run Date Time',
history.run_duration AS 'Duration in Second'
FROM msdb.dbo.sysjobs jobs
INNER JOIN msdb.dbo.sysjobhistory history
ON jobs.job_id = history.job_id
WHERE jobs.enabled = 1 

The script is very simple and it returns details of all the enabled job. You can also remove the last where condition and it will return all the jobs on your system. While doing SQL Server performance tuning, I often ask my client questions about the jobs which are running currently on their server. My experience says there are always few jobs which no one knows why are running on the server and often they negatively impact the performance.

Here are few additional blog posts related to SQL Server Job History:

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL DMV, SQL Jobs, SQL Scripts, SQL Server, SQL Server Agent
Previous Post
SQL SERVER – How to Write Correlated Subquery?
Next Post
SQL SERVER – Performing IF…THEN In T-SQL With IIF Expression

Related Posts

5 Comments. Leave new

  • Dominic Wirth
    January 4, 2019 2:47 pm

    Here is a script that I created and which goes in the same direction. It shows and overview of all jobs and gives some statistic informations.

    WITH JobCounters AS
    ,MIN(run_duration) AS MinRunDurationSeconds
    ,MAX(run_duration) AS MaxRunDurationSeconds
    ,MAX([msdb].[dbo].[agent_datetime]([run_date], [run_time])) AS LastStartDateTime
    ,AVG(run_duration) AS AvgRunDurationSeconds
    FROM [msdb].[dbo].[sysjobhistory]
    WHERE step_id = 0 — Job Outcome
    GROUP BY job_id
    J.[name] AS JobName
    ,JC.[name] AS JobCategory
    ,CAST((CASE WHEN J.[enabled] = 1 THEN 1 ELSE 0 END) AS BIT) AS IsEnabled
    WHEN LastJobExecution.run_status = 2 THEN ‘RETRY_STEP’
    WHEN JA.job_id IS NOT NULL AND JA.stop_execution_date IS NULL AND JA.start_execution_date IS NOT NULL THEN ‘EXECUTING…’
    WHEN LastJobExecution.run_status = 0 THEN ‘ERROR’
    WHEN LastJobExecution.run_status = 1 THEN ‘SUCCESS’
    WHEN LastJobExecution.run_status = 3 THEN ‘ABORT’
    END) AS LastRunStatus
    ,COALESCE(JA.start_execution_date, JobCounters.LastStartDateTime) AS LastStartDateTime
    ,JA.next_scheduled_run_date AS NextStartDateTime
    ,(CASE JA.run_requested_source WHEN 1 THEN ‘SQL Agent’ WHEN 2 THEN ‘Alarming’ WHEN 3 THEN ‘Boot’ WHEN 4 THEN ‘User’ ELSE JA.run_requested_source END) AS StartedBy
    ,JS.step_name AS LastJobStep
    ,LastJobExecution.run_duration AS LastRunDurationSeconds
    FROM [msdb].[dbo].[sysjobs] AS J
    LEFT JOIN [msdb].[dbo].[syscategories] AS JC ON J.category_id = JC.category_id
    LEFT JOIN JobCounters ON J.job_id = JobCounters.job_id
    LEFT JOIN [msdb].[dbo].[sysjobactivity] JA ON J.job_id = JA.job_id
    LEFT JOIN [msdb].[dbo].[sysjobsteps] JS ON JA.job_id = JS.job_id AND JA.last_executed_step_id = JS.step_id
    SELECT run_duration, run_status, step_id, job_id, run_date, run_time, retries_attempted, [message], ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY instance_id DESC) AS RowId
    FROM msdb.dbo.[sysjobhistory]
    ) AS LastJobExecution ON J.job_id = LastJobExecution.job_id AND ISNULL(LastJobExecution.RowId, 1) = 1
    JA.session_id IS NULL
    OR EXISTS (SELECT NULL FROM [msdb].[dbo].[sysjobactivity] GROUP BY job_id HAVING job_id = J.job_id AND MAX(session_id) = JA.session_id)
    ORDER BY J.[name];

  • Thanks so much for sharing such valuable script

  • Ashley Hoff
    July 23, 2019 5:23 am

    OK, from my experience, the run duration is not in seconds. The run duration is a representation of the time run, with the specific separators removed. For example, I have a job that runs for just over an hour. The run duration is represented as 10212 via the script above.

    This value represents 01:02:12, being 1 hour, 2 minutes and 12 seconds.

    Please keep this in mind when using this script.

  • Hi all,
    I need a small help in order to improve my knowledge. I have archive the SSIS job history of all SSIS jobs rather than purging it. At the time of requirement, we can restore the history from Archived table. In that case do we need to Archive the data of only table [msdb].[dbo].[sysjobhistory], OR any other table I have to include in Archival Activity? If someone can confirm, that will be helpful.
    Thanks in advance.


Leave a ReplyCancel reply

Exit mobile version