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

Dorman

Answer 1

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.

Dorman

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

Crucible

Thanks for the info on sysjobhistory, I'm afraid to be dependent on the job name though. – Dorman

thought this may have been easier for you. It's even more simple without the job name! – Crucible

yeah, but I still need to get the job_id – Dorman

Hmmm.. you can get that manually by looking at msdb..sysjobs & hardcode it in your script?. The job_id wont change unless you delete it. If you don't know what the job_id is & it can change, stick with the jobname – Crucible

The answer I've provided demonstrates how to get the job_id without relying on the job name. – Dorman

oh. tricky, i like it! will obviously only work if run inside the job – Crucible

I'm trying something similar, but I have two problems: 1) Is the run_date and run_time in UTC or local time? If it's local time, I need to account for a time-zone change. 2) What's the best way to change these two items from number strings to a Javascript Date() or moment.js moment()? – Cohette

Answer 3

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.

Austere

Answer 4

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')

Sherri

Answer 5

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

Superheterodyne

Answer 6

This code is very fast!

  DECLARE 
        @job_id uniqueidentifier,
        @last_run_date VARCHAR(8)
    
    -- Sample Job Id from msdb.dbo.sysjobs
    
    SET @job_id='81D49849-40A2-41AB-B19B-B7ACADA83339'
    
    
    SELECT TOP (1)
         @last_run_date=run_date
    FROM msdb.dbo.sysjobhistory 
    WHERE 
        job_id = @job_id
        AND run_status = 1
    ORDER BY
        CONVERT(BIGINT,run_date)*1000000+CONVERT(INT,run_time) DESC

Thrombus

Answer 7

This code return the last execution date and time of all active jobs..

SELECT 
    *
FROM
(
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY T1.job_id ORDER BY CONVERT(BIGINT,T1.run_date)*1000000+CONVERT(INT,T1.run_time) DESC) AS RowId,
         T2.name,
         T1.step_name,
         FORMAT(T1.run_date,'0000/00/00') AS run_date,
         FORMAT(T1.run_time,'00:00:00') AS run_time
    FROM 
        msdb.dbo.sysjobhistory AS T1 WITH (NOLOCK) INNER JOIN
        msdb.dbo.sysjobs AS T2 WITH (NOLOCK) ON T2.job_id = T1.job_id
        
    WHERE 
        T1.run_status = 1
) AS TABLE1 
WHERE TABLE1.RowId=1

Thrombus