This article will enlight on how to examine SQL Server Agent job status. Obviously you can use SQL Server Agent's Job Activity Monitor window. However, we will accomplish it using T-SQL. The Job information resides in sysjobs table and corresponding history in sysjobhistory of msdb. Let's combine:
-- Declare a table variable to store the inform temporarily
Declare @tab table(
name sysname,
run_status varchar(20),
message nvarchar(1024),
lastrun datetime
)
-- Open a cursor to read the active job list
DECLARE Users CURSOR FOR
Select job_id from msdb..sysjobs where enabled = 1
declare @job_id uniqueidentifier
-- Loop through cursor
OPEN Users
FETCH NEXT FROM Users INTO @job_id
WHILE @@fetch_status <> -1
BEGIN
IF @@fetch_status = 0
BEGIN
-- Add in table variable
Insert into @tab (name,run_status,message,lastrun)
SELECT top 1 j.name, case when run_status = 1 then 'SUCCESS' else 'FAILED' end as run_status, message, endTime = DATEADD
(
SECOND,
jh.run_duration,
CAST
(
CONVERT(VARCHAR, jh.run_date)
+ ' ' + STUFF(STUFF(RIGHT('000000'
+ CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':')
AS DATETIME
)
)
FROM msdb..sysjobhistory jh INNER JOIN msdb..sysjobs j ON j.job_id = jh.job_id
WHERE jh.step_name = '(Job outcome)'
AND j.job_id = @job_id
Order by endTime desc
END
FETCH NEXT FROM Users INTO @job_id
END
DEALLOCATE Users
-- Display all the records.
SELECT * FROM @tab
That's it. Have a nice day!