Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

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

31 January,2019 by Tom Collins

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.


@@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: Tom Collins (


Verify your Comment

Previewing your Comment

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

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.


Post a comment on Check SQL Agent Job Exceution Status and Messages Generated with a sql script | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer