T-SQL Stored Procedure to Get the Last Job Run Time and Status

/*
Stored procedure to run the last run time and run satus of a SQL job.

SQL Version: 2005+

Last modified on 2013-07-03 by Nick Xu

--Usage
--returns the last successful run time
exec sp__GetLastJobRunTimeStatus @jobname = N'syspolicy_purge_history', @status = N'success'

--returns the last failed runtime
exec sp__GetLastJobRunTimeStatus @jobname = N'syspolicy_purge_history', @status = N'fail'
*/
use master
go

if exists (select * from sysobjects where name = N'sp__GetLastJobRunTimeStatus' and type = N'P')
begin
	drop proc sp__GetLastJobRunTimeStatus
end
go

create proc sp__GetLastJobRunTimeStatus
@jobname as varchar(100) = N'syspolicy_purge_history',
@status as varchar(10) = N'success'
as
begin

declare @run_status as int

if (upper(@status) = N'FAIL')
	begin
		set @run_status = 0
	end
else
	begin
		set @run_status = 1
	end

SELECT j.[name] as JobName,
	   
	   "RunSatus" = 
	   case 
			when jh.run_status = 1 then N'SUCCESS'
			else N'FAIL'
	   end,

	   MAX(CAST(STUFF(STUFF(CAST(jh.run_date as varchar),7,0,'-'),5,0,'-') + ' ' + STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),' ','0'),5,0,':'),3,0,':') as datetime)) AS [LastRunTime]
FROM msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobhistory jh 
ON jh.job_id = j.job_id AND jh.step_id = 0 
WHERE j.[name] = @jobname
GROUP BY j.[name], jh.run_status
HAVING jh.run_status = @run_status 
end
go
Advertisements

About Nick Xu

Not a DBA anymore
This entry was posted in SQL Server, T-SQL Script and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s