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.

Solarwinds

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)

Solarwinds
, , , ,
Previous Post
SQL SERVER – How to Write Correlated Subquery?
Next Post
SQL SERVER – Performing IF…THEN In T-SQL With IIF Expression

Related Posts

4 Comments. Leave new

  • 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
    (
    SELECT
    job_id
    ,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
    )
    SELECT
    J.[name] AS JobName
    ,JC.[name] AS JobCategory
    ,CAST((CASE WHEN J.[enabled] = 1 THEN 1 ELSE 0 END) AS BIT) AS IsEnabled
    ,(CASE
    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’
    ELSE NULL
    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
    ,JobCounters.MinRunDurationSeconds
    ,JobCounters.MaxRunDurationSeconds
    ,JobCounters.AvgRunDurationSeconds
    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
    LEFT JOIN (
    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
    WHERE
    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];

    Reply
  • Thanks so much for sharing such valuable script

    Reply
  • 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.

    Reply

Leave a Reply

Menu