Get Status of SQL Agent Jobs – SQL Server

There are couple of tables (sysjobs, sysjobhistory) in msdb database which hold information about SQL Agent jobs.
sysjobs table contains: name, enabled, notify information, date_created… etc
sysjobhistory table contains:run_status, run_date, run_time, server… etc

Script below will return jobName, message, enabled, run_status, run_date. Run_status: 0=Failed, 1=Succeeded, 2=Retry, Canceled=3, Steps within jobs=4

SELECT DISTINCT SERVERPROPERTY('ServerName') AS [ServerName],
j.name AS jobName, jh.message, j.enabled, jh.run_status, jh.run_date FROM msdb.dbo.sysjobs j
INNER join msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id
WHERE 1=1
AND enabled = 1

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>