December 19, 2005 at 11:59 am
Hello All,
Very occasionally I come across a situation where a SQL Server (2000 - SP4) job doesn't run when it was scheduled to (i.e. it doesn't fail, it just doesn't run at all). There usually end up being a reasonable explanation for this, but as I only have failure notification set up, I often don't find out about it until some time later when whatever consequences have happened.
Rather than setting every job to notify me when it's completed and then trawling through the list to see if anything is missing each day, is there any way of being notified when a job doesn't run when it's scheduled to?
Chris
December 19, 2005 at 12:31 pm
Chris,
in my experience there are 3 possible reasons why a job would not run.
1. SQL Agent is not running
2. The schedule isn't enabled
3. The scheduled time has already passed
For the first one you could monitor the SQL Agent service through MOM or some other tool.
The two other ones in SQL 2000 you could query the sysjobschedules table. But be carefull, in 2005 this table has changed and you probably have to use a new system view, but I haven't found the best method yet.
Markus
[font="Verdana"]Markus Bohse[/font]
December 28, 2005 at 8:04 am
OK, thanks Markus.
December 28, 2005 at 9:58 am
I have a scheduled job that runs the following script on a daily basis...
exec master.dbo.xp_sendmail 'youremailaddress',
@subject=' Job Failures',
@query='
select b.server, a.name, b.step_name, b.sql_message_id, b.sql_severity,
b.message
from msdb.dbo.sysjobs a, msdb.dbo.sysjobhistory b
where a.job_id = b.job_id
and (b.run_date = convert(int,convert(varchar,getdate(),112)) or
b.run_date + 1 = convert(int,convert(varchar,getdate(),112)))
and b.run_status in (0,3)'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply