Monitor SQL Server Agent Job History Data with T-SQL

All the jobs information are stored in system tables of MSDB system database. Here we are going to use sysjobs and sysjobhistory tables to monitor information for each scheduled jobs execution. Below query will help you to monitor jobs name, time and status messages so that you can monitor your jobs easily with a single query and date time format can be seen perfectly with SQL function agent_datetime() by passing parameter for date and time columns.

SELECT sj.name, msdb.dbo.agent_datetime(sjh.run_date,sjh.run_time) AS TIME, sjh.message
FROM msdb.dbo.sysjobhistory AS SJH
INNER JOIN msdb.dbo.sysjobs AS SJ
ON sjh.job_id = sj.job_id
WHERE sjh.step_id = 0
ORDER BY TIME DESC

Output of the query is given in below snap.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]