Time Job Last Ran

I recently had a request from a client to allow him to show on his reports when his datamart had last been updated by an SSIS package that was run on a schedule.

There are quite a few scripts to perform this task on the web, I felt that they are all over complicated and hard for a semi-technical person to follow modify should their needs change. (I like to leave my clients with code that they can understand and trust that they will want me back because I’m good at what I do rather than because I’m the only person that can understand the code I left them with)  J



The following short scrip is what I came up with to meet this requirement. As you can see, it only accesses 3 tables in the msdb database and can be followed by anyone with a basic understanding of T-SQL.



There are a number of other fields in these tables that might add value to your particular project, I’ll leave it to you to explore them and see what works for you.

declare @Job_Name varchar(8000)

set @Job_Name = ''            -- insert your job name here

select            -- collect the history for the required job

      j.name as Job_Name

      ,a.start_execution_date as date_last_ran

      ,(    select MAX(a1.next_scheduled_run_date)

            from msdb.dbo.sysjobactivity a1

            where a1.job_id = j.job_id

      )     -- and the time it is next sceduled to run

from

      msdb.dbo.sysjobs j

      inner join msdb.dbo.sysjobactivity a on j.job_id = a.job_id

      inner join msdb.dbo.sysjobhistory h on a.job_history_id = h.instance_id

            and h.run_status = 1    -- limit to only succesful runs

where

      j.name = @Job_Name

While it runs against table sin msdb you can happily put it anywhere, just so long as the user it runs under has read rights on the 3 tables. (I wrapped it in a stored procedure and put it in his datamart)

Naturally this comes with the standard disclaimers that apply to any code you might find for free on the web.