September 23, 2013 at 6:34 am
Hi friends,
I have around 120 jobs in a SQL instance. Some of the jobs are scheduled on hourly basis and some of the jobs are scheduled on daily basis and some of the jobs are scheduled on weekly basis. Is there a way to get the failed job details in such a way that, it explains that this is hourly job this is daily job this is weekly job.
Thanks in advance.
September 23, 2013 at 7:54 am
This should work fairly well:
SELECT name, MAX(last_executed_step_date) AS LastRunDate,MAX(next_scheduled_run_date) AS NextRunDate,
CASE WHEN DATEDIFF(HOUR,MAX(last_executed_step_date),MAX(next_scheduled_run_date)) <= 24 THEN 'Daily'
WHEN DATEDIFF(DAY,MAX(last_executed_step_date),MAX(next_scheduled_run_date)) = 7 THEN 'Weekly'
WHEN DATEDIFF(MONTH,MAX(last_executed_step_date),MAX(next_scheduled_run_date)) = 1
AND DATEPART(DAY,MAX(last_executed_step_date)) = DATEPART(DAY,MAX(next_scheduled_run_date)) THEN 'Monthly'
END AS ExecutionPeriod
FROM msdb.dbo.sysjobactivity
INNER JOIN
msdb.dbo.sysjobs
ON sysjobactivity.job_id = sysjobs.job_id
INNER JOIN msdb.dbo.sysjobhistory
ON sysjobactivity.job_id = sysjobhistory.job_id
WHERE run_status = 0
AND CONVERT(datetime,CONVERT(varchar(8),run_date),112) = CONVERT(Date,last_executed_step_date)
GROUP BY name
This will get a list of all jobs that failed on their last execution, their start date and time on the last failed attempt, and the date and time of their next attempted execution. Some manual examination will be needed on this table to determine the scheduling, but it should be easy to eyeball it if there aren't too many failures. Some extra coding could be put together for the date/time calculations if those are needed.
EDIT: Actually, calculating the time periods was simpler than I thought, but it depends greatly on whether the jobs run at the same time across their recurring periods (they probably should, but I've seen otherwise!). If they do, this should work handily.
RE-EDIT: Blah! My last version would report back on any jobs that had failed at all; so if something failed a week ago but succeeded since then, it would show up. Not good. I've revised the original coding again to account for this. Seems to be a bit messy, though.
- 😀
September 24, 2013 at 10:21 am
Thanks a lot
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply