Post reply

How to check the last time the job got succeeded in SQL Server 00/05/08

  • May 20, 2009 at 10:05 pm

    #200666

    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.

  • May 20, 2009 at 11:58 pm

    #998083

    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/[/url]

    http://www.sqlservercentral.com/scripts/Miscellaneous/30159/[/url]

    Tanx 😀

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply