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

Have a look at this article, it may point you in the right direction. Unfortunately I don't have SQL Server on my home machine so can't test it out for you!

You basically need to query the sysjobactivity table and get the values from start_execution_date and stop_execution_date. You'll need the job_id, but i'm not sure where you'll get that from.

I hope this helps.

EDIT Ok, I've done some more research and found the following code snippet

DECLARE @jobId binary(16)

SELECT @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name = N'Name of Your Job')

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 & the history is not large enough, you will end up with no history.

Try this below. (UPDATED TO USE JOB_ID) As you can see, you have to convert the run time back to a date, as SQL Server stores it as int!

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..sysjobs sj 
from msdb..sysjobhistory
where step_id=0 and run_status=1 and job_id=@job_id
order by run_datetime desc

Answer 3

Using information from the following threads:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112427 http://www.sqlservercentral.com/Forums/Topic542581-145-1.aspx

This is what I came up with...

DECLARE 
    @statement nvarchar(72),
    @job_id uniqueidentifier,
    @last_run_date datetime

SET @statement = 'SET @guid = CAST(' + SUBSTRING(APP_NAME(), 30, 34) + ' as uniqueidentifier)'

EXECUTE sp_executesql @statement, N'@guid uniqueidentifier OUT', @guid = @job_id OUT

SELECT TOP (1)
    @last_run_date = CAST(STR(run_date, 8, 0) as datetime) + CAST(STUFF(STUFF(STR(run_time, 6, 0), 3, 0, ':'), 6, 0, ':') as datetime)
FROM msdb.dbo.sysjobhistory 
WHERE job_id = @job_id
AND run_status = 1
ORDER BY
    CAST(STR(run_date, 8, 0) as datetime) + CAST(STUFF(STUFF(STR(run_time, 6, 0), 3, 0, ':'), 6, 0, ':') as datetime) DESC

EXEC dbo.usp_UpdateFrom @last_run_date

I'm not particularly comfortable with this, but I prefer this method of getting the job_id over depending on the job name.