Sunday 14 October 2012

How to find failed and successul jobs on sql server for particuler date?

                Daily Monitoring   For Failed  Jobs in Sql Server
 select sj.[name],sh.step_id,sh.step_name,sh.run_date,sh.run_time,sh.sql_severity ,sh.message,
sh.server from msdb.dbo.sysjobs sj inner join msdb.dbo.sysjobhistory sh
on sh.job_id=sj.job_id
inner join msdb.dbo.sysjobsteps ss
on sj.job_id=ss.job_id and
sh.step_id=ss.step_id
where sh.run_status=0
and sh.run_date='20101115'
and sh.message like '%failed%'

          Daily Monitoring for successful jobs in Sql Server

     select sj.[name],sh.step_id,sh.step_name,sh.run_date,sh.run_time,sh.sql_severity ,sh.message,
sh.server from msdb.dbo.sysjobs sj inner join msdb.dbo.sysjobhistory sh
on sh.job_id=sj.job_id
inner join msdb.dbo.sysjobsteps ss
on sj.job_id=ss.job_id and
sh.step_id=ss.step_id
where sh.run_status=1
sh.run_date='20101115'

No comments:

Post a Comment