February 2, 2010 at 12:08 pm
Hi,
how i can retrieve all failed sql agent jobs that failed this week (7 days from getdate())
select jobs.name as 'JOB_NAME',his.message as 'MESSAGE',SUBSTRING(cast(his.run_date as varchar(9)),7,2) + '/' + SUBSTRING(cast(his.run_date as varchar(9)),5,2) + '/' + SUBSTRING(cast(his.run_date as varchar(9)),1,4) + ' ' + SUBSTRING(cast(his.run_time as varchar(9)),1,2) + ':' + SUBSTRING(cast(his.run_time as varchar(9)),3,2) + ':' + SUBSTRING(cast(his.run_time as varchar(9)),1,2) as 'RUN_TIME'
from msdb..sysjobhistory his,msdb..sysjobs jobs
where his.job_id = jobs.job_id
and his.run_status in (0,2,3)
and his.step_id = 1
order by jobs.name asc,his.run_date desc
THX
February 2, 2010 at 12:33 pm
Using your basic script you can just add this to it:
SELECT jobs.name as 'JOB_NAME',his.message as 'MESSAGE',SUBSTRING(cast(his.run_date as varchar(9)),7,2) + '/' + SUBSTRING(cast(his.run_date as varchar(9)),5,2) + '/' + SUBSTRING(cast(his.run_date as varchar(9)),1,4) + ' ' + SUBSTRING(cast(his.run_time as varchar(9)),1,2) + ':' + SUBSTRING(cast(his.run_time as varchar(9)),3,2) + ':' + SUBSTRING(cast(his.run_time as varchar(9)),1,2) as 'RUN_TIME'
from msdb..sysjobhistory his,msdb..sysjobs jobs
where his.job_id = jobs.job_id
and his.run_status in (0,2,3)
and his.step_id = 1
and abs(datediff(dd, getdate(), Cast(DATEADD(second,(((run_time*1.0)/100)-CAST(run_time/100 AS INT))*100,
DATEADD(minute,(run_time/100)-(run_time/10000*100),
DATEADD(hour,run_time/10000, CAST(run_date AS CHAR(8))))) as datetime))) < 7
order by jobs.name asc,his.run_date desc
That should get it for you.
February 3, 2010 at 1:51 pm
Your script returns incorrect time when job executes before noon time.
Use this:
SELECT jobs.name as 'JOB_NAME',his.message as 'MESSAGE',
ISNULL(SUBSTRING(CONVERT(varchar(8),his.run_date),7,2) + '/'
+ SUBSTRING(CONVERT(varchar(8),his.run_date),5,2) + '/'
+ SUBSTRING(CONVERT(varchar(8),his.run_date),1,4),'') +space(1)+
ISNULL(SUBSTRING(CONVERT(varchar(7),his.run_time+1000000),2,2) + ':'
+ SUBSTRING(CONVERT(varchar(7),his.run_time+1000000),4,2) + ':'
+ SUBSTRING(CONVERT(varchar(7),his.run_time+1000000),6,2),'') as 'RUN_TIME'
from msdb..sysjobhistory his,msdb..sysjobs jobs
where his.job_id = jobs.job_id
and his.run_status in (0,2,3)
and his.step_id = 1
and abs(datediff(dd, getdate(), Cast(DATEADD(second,(((run_time*1.0)/100)-CAST(run_time/100 AS INT))*100,
DATEADD(minute,(run_time/100)-(run_time/10000*100),
DATEADD(hour,run_time/10000, CAST(run_date AS CHAR(8))))) as datetime))) < 7
order by jobs.name asc,his.run_date desc
MJ
February 4, 2010 at 12:35 am
Thanks to you all
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply