Get date of last successful job run?

Question

I have a single step job that executes a stored procedure. I would like get the date of the last successful job execution time so that I can just update a delta instead of the whole set of data.

Right now I have the job setup to run once every day, so I have a default parameter that if it's null I set it to GETDATE() - 1 so I'm still updating a delta but what I'd like to do is set the date to the last successful execution of the job.

exec dbo.usp_UpdateFrom @LastSuccessfulExecutionTime

Current procedure is something like

CREATE PROCEDURE dbo.usp_UpdateFrom
    @FromDate datetime = NULL --would like to pass last successful execution time of the job
AS
    IF @FromDate IS NULL
        SET @FromDate = GETDATE() - 1

    -- do stuff
END

Answer 1

Since sysjobhistory only maintains a certain number of records, I recomend using sysjobactivity, which keeps the last execution "history" of each job and session.

SELECT TOP 1 start_execution_date
FROM msdb.dbo.sysjobactivity
WHERE run_requested_date IS NOT NULL
AND job_id = @job_id
ORDER BY session_id DESC;

NOTE: If a Job has not been executed during the life of a session, almost all values will be null.

ALSO there is a system Stored Procedure sp_help_job that returns this information. It accepts job_id, enabled, etc. as parameters to return 1 or more records.

Answer 2

The tables you want are sysjobs and sysjobhistory in msdb. Although be warned! SQL Server only maintains a certain number of records, so if there are too many jobs and the history is not large enough, you will end up with no history.

The following code retrieves the job_id for the given job name, and queries the history table for the last successfully finished run (i.e. step 0, status 1). As you can see, you have to convert the run time back to a date, as SQL Server stores it in two int columns:

DECLARE @job_id binary(16)
SELECT @job_id = job_id FROM msdb.dbo.sysjobs WHERE (name = N'YourJobName')

SELECT TOP 1
    CONVERT(DATETIME, RTRIM(run_date))
    + ((run_time / 10000 * 3600) 
    + ((run_time % 10000) / 100 * 60) 
    + (run_time % 10000) % 100) / (86399.9964) AS run_datetime
    , *
FROM
    msdb..sysjobhistory sjh
WHERE
    sjh.step_id = 0 
    AND sjh.run_status = 1 
    AND sjh.job_id = @job_id
ORDER BY
    run_datetime DESC

Answer 3

To get Last successfully run jobs:

SELECT 
    h.[job_id]
    ,j.Name JobName
    ,CONVERT(CHAR(10), CAST(STR(run_date,8, 0) AS dateTIME), 111)   [LastRunDate]
   ,STUFF(STUFF(RIGHT('000000' + 
    CAST (run_time AS`` VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') [LastRunTime]
   ,CASE run_status 
    WHEN 0 THEN 'Failed' 
    WHEN 1 THEN 'Succeeded' 
    WHEN 2 THEN 'Retry' 
    WHEN 3 THEN 'Cancelled' 
    WHEN 4 THEN 'In Progress' 
    END AS ExecutionStatus                                                 
    FROM [msdb].[dbo].[sysjobhistory] h 
    JOIN msdb.dbo.sysjobs j ON h.job_id=j.job_id 
    WHERE run_status=1 
    ORDER BY run_date DESC,run_time DESC