Below query can be used to get list of all the SQL Agent Job details in SQL Server.

use msdb;
SELECT Convert(varchar(22),SERVERPROPERTY('ServerName')) AS 'ServerName',
convert(varchar(35),j.name) as 'JobName',
CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END AS 'JobStatus',
CASE jh.run_status WHEN 0 THEN 'Error Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress' ELSE
'Status Unknown' END AS 'LastRunStatus',
convert(varchar(20),ja.run_requested_date) as 'LastRunDate',
CONVERT(VARCHAR(14),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS 'RunDuration',
convert(varchar(24),ja.next_scheduled_run_date) as 'NextScheduledRunDate'
--CONVERT(VARCHAR(500),jh.message) AS step_description
(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)
join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id
WHERE ja.session_id=(SELECT MAX(session_id) from msdb.dbo.sysjobactivity) ORDER BY 'jobname','JobStatus';

Sample Output:

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.