Here is a useful query for getting a list of failed jobs from SQL
Server. If you are responsible for tracking and logging job failures, this administrative work can be overwhelming in a busy week. This query will list all jobs that
failed during a given time period. It uses a function that I created,
convert_run_datetime.
<code>
select a.name, b.step_id, b.step_name,
max(master.dbo.convert_run_datetime(b.run_date,b.run_time)) max_rundt,
min(master.dbo.convert_run_datetime(b.run_date,b.run_time)) min_rundt,
count(*) number_of_failures
from sysjobhistory b, sysjobs a
where master.dbo.convert_run_datetime(b.run_date,b.run_time)
between ’07/24/2006 8:00:00 am’ and ’07/31/2006 9:00:00 am’
and b.run_status = 0
and b.step_id > 0
and a.job_id = b.job_id
group by a.name, b.step_id, b.step_name
</code>
powered by performancing firefox
Tags: Other, SQL Server, SQL Tricks