Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

How to check the last time the job got succeeded in SQL Server 00/05/08 Expand / Collapse
Posted Wednesday, May 20, 2009 10:05 PM


Group: General Forum Members
Last Login: Friday, July 3, 2015 3:31 AM
Points: 20, Visits: 508
I have a job that is failing from ages. The history for this job shows that it never got succeeded. Is there any other chance to check the last time the job got succeeded.
Post #721097
Posted Wednesday, May 20, 2009 11:58 PM



Group: General Forum Members
Last Login: 2 days ago @ 6:48 PM
Points: 1,639, Visits: 1,074

CREATE PROCEDURE usp_job_history
@dateparam DATETIME
SELECT dbo.sysjobhistory.server, AS job_name,
CASE dbo.sysjobhistory.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
ELSE '???'
END as run_status, dbo.sysjobhistory.run_date, dbo.sysjobhistory.run_time, dbo.sysjobhistory.step_id, dbo.sysjobhistory.step_name, dbo.sysjobhistory.run_duration, dbo.sysjobhistory.message
FROM dbo.sysjobhistory INNER JOIN
dbo.sysjobs ON dbo.sysjobhistory.job_id = dbo.sysjobs.job_id
WHERE dbo.sysjobs.category_id = 0 and dbo.sysjobhistory.run_date = datepart(yyyy,@dateparam)*10000 + datepart(mm,@dateparam)*100 + datepart(dd,@dateparam)
ORDER BY dbo.sysjobhistory.server, dbo.sysjobhistory.run_date, dbo.sysjobhistory.run_time,, dbo.sysjobhistory.step_id

--Example: EXEC usp_job_history '10/02/2004'

Check out

Post #721125
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse