Thursday, September 21, 2017

Last run date of job history more than 90 days



declare @lastrun varchar(20)
declare @diff varchar(20)
set @lastrun=(select MIN(run_date)from msdb.dbo.sysjobhistory)
set @diff=(select DATEDIFF(DAY, @lastrun, Getdate()))
print @diff
--print @lastrun
if(@diff>0)
begin
--print @lastrun
SELECT j.[name],
case j.enabled when 0 then 'Disabled'
               when 1 then 'enabled'
      End as [enabled],
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 [LastRun],
CASE jh.run_status WHEN 0 THEN 'Failed'
                   WHEN 1 THEN 'Success'
                   WHEN 2 THEN 'Retry'
                   WHEN 3 THEN 'Canceled'
                   WHEN 4 THEN 'In progress'
                   END AS [Status]
into #tempjobhistory
FROM (SELECT a.job_id,MAX(a.instance_id) As [instance_id]
FROM msdb.dbo.sysjobhistory a
WHERE a.step_id = 0
GROUP BY a.job_id) b
INNER JOIN msdb.dbo.sysjobhistory jh ON jh.instance_id=b.instance_id
INNER JOIN msdb.dbo.sysjobs j ON j.job_id = jh.job_id

select * from #tempjobhistory where DATEDIFF(DAY, [LastRun], Getdate()) >=90
drop table #tempjobhistory
end
else
print 'Table having History less than 90 days'

No comments:

Post a Comment