Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
Author
Message
Posted Wednesday, May 20, 2009 10:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 4, 2014 9:51 AM
Points: 14, Visits: 476
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


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 28, 2013 8:50 AM
Points: 1,606, Visits: 1,039



CREATE PROCEDURE usp_job_history
@dateparam DATETIME
AS
SELECT dbo.sysjobhistory.server, dbo.sysjobs.name 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.sysjobs.name, dbo.sysjobhistory.step_id
GO

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




Check out

http://www.sqlservercentral.com/scripts/Miscellaneous/31277/


http://www.sqlservercentral.com/scripts/Miscellaneous/30159/



Tanx
Post #721125
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse