Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
druer
Constraint Violating Yak Guru
314 Posts |
 Posted - 2006-04-21 : 13:33:08
|
I got sick and tired of having jobs fail without being notified, because the job was marked as "in process"  and never completed its failure, so a failure notice wasn't sent. So I wrote the following script to find all of the jobs that are enabled and email me the last date associated with the job and its success/failure status. This way if a job is really hung up because of a SQL bug, I get notified and can see that its last success date was in the past so I'm clued in to go and take action.[CODE]declare @Command varchar(2000)select @Command = 'select sh.Run_Date, substring(sj.name,1, 40) as Name, substring(sh.Message,1,25) as JobOutcomefrom msdb.dbo.sysjobs sj inner join (select job_id, max(instance_id) as LastRunfrom msdb.dbo.sysjobhistory where job_id in (select job_id from msdb.dbo.sysjobschedules) and step_id = 0 group by job_id) Outcome on Outcome.Job_id = sj.Job_id and sj.enabled = 1inner join msdb.dbo.sysjobhistory sh on sh.job_id = sj.job_id and sh.instance_id = Outcome.LastRun order by sj.name'exec master..xp_sendmail @recipients = 'email.goes@here', @subject = 'Server: Job Outcomes', @query = @Command[/CODE] |
|
|
|
|
|
Checking Job Outcomes