Sunday, 27 December 2009

Findout failure job list from your Server

Recenly I was searching for a query to findout the list of failed jobs from my Database server and send an email to my mail box.

Hope this could help you

Select top 100 * from msdb.dbo.sysjobhistory
where job_id IN (Select job_id from msdb.dbo.sysjobs_view Where enabled=1 )
and step_id=1 and run_date=convert(varchar, getdate(), 112)
and run_status=0
order by instance_id desc