Check SQL Agent Job Exceution Status and Messages Generated with a sql script

31 January,2019 by Jack Vamvas

Question: How can I check SQL Agent Job Execution  Status using t-sql?  I want to be able to report on the Job Steps execution status , run time duration and Message Generated.

Do you have  a script example , which returns the above values based on the last 24 hrs.

Answer:

SELECT 
@@servername AS 'Server_Name',
j.NAME AS 'Job_Name',
CASE h.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
        WHEN 4 THEN 'In Progress'
     END AS ExecutionStatus

    ,s.step_id AS 'Step'
    ,s.step_name AS 'Step_Name'
    ,msdb.dbo.agent_datetime(run_date, run_time) AS 'Run_DateTime'
    ,((run_duration / 10000 * 3600 + (run_duration / 100) % 100 * 60 + run_duration % 100 + 31) / 60) AS 'Run_DurationMinutes'
     ,h.message AS 'Message_Generated'
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
INNER JOIN msdb.dbo.sysjobhistory h ON s.job_id = h.job_id
    AND s.step_id = h.step_id
    AND h.step_id <> 0
WHERE j.enabled = 1 --Only Enabled Jobs
  and h.run_status = 0
  and DATEDIFF(hh, msdb.dbo.agent_datetime(run_date,run_time),  GETDATE()) <= 24
ORDER BY Job_Name    ,Run_DateTime DESC



Extra reading on SQL Agent Jobs

List enabled status of SQL Server Agent Jobs (SQL Server DBA)

SQL Server Agent Top Tips (SQL Server DBA)

Send sql server agent job history through email notification


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Check SQL Agent Job Exceution Status and Messages Generated with a sql script


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer