check job status

17 May 2005 8:26 PM

ChrisR

I want to have a query that can tell me all the jobs on a server that have
failed and need to be run again in the future. This is what I have come up
with:

select Server = 'BoxName', SJ.Name
from BoxName.msdb.dbo.SysJobHistory SJH
inner join BoxName.msdb.dbo.SysJobs SJ on SJH.job_id = SJ.job_id
inner join BoxName.msdb.dbo.SysJobSchedules SJS on SJ.Job_id = SJS.job_id
where SJH.run_status = 0
and SJS.enabled = 1
and SJS.next_run_date <> 0
and SJS.next_run_time <> 0
group by Server, SJ.Name

The problem though is it would appear that SQL doesn't update the run_status
in the SysJobHistory table very often as I am getting values returned from
this query that did fail several hours ago, but have since succeeded. Does
anyone know of a better way to write this query? I know I can setup
Alerting, but need a backup for it.

TIA, ChrisR

18 May 2005 1:40 AM

Sue Hoegemeier

Take a look at sp_help_job. Or look at the sql for
sp_help_job. It sounds like you may actually be looking for
last_run_outcome which sp_help_job obtains from sysjobsteps.

-Sue

On Tue, 17 May 2005 13:26:56 -0700, "ChrisR"
<noem***@bla.com> wrote:

Show quoteHide quote

>I want to have a query that can tell me all the jobs on a server that have
>failed and need to be run again in the future. This is what I have come up
>with:
>
>select Server = 'BoxName', SJ.Name
>from BoxName.msdb.dbo.SysJobHistory SJH
>inner join BoxName.msdb.dbo.SysJobs SJ on SJH.job_id = SJ.job_id
>inner join BoxName.msdb.dbo.SysJobSchedules SJS on SJ.Job_id = SJS.job_id
>where SJH.run_status = 0
>and SJS.enabled = 1
>and SJS.next_run_date <> 0
>and SJS.next_run_time <> 0
>group by Server, SJ.Name
>
>
>The problem though is it would appear that SQL doesn't update the run_status
>in the SysJobHistory table very often as I am getting values returned from
>this query that did fail several hours ago, but have since succeeded. Does
>anyone know of a better way to write this query? I know I can setup
>Alerting, but need a backup for it.
>
>TIA, ChrisR
>

18 May 2005 4:03 PM

ChrisR

Thanks Sue. The problem that I'm having though (for example) is that I have
a job used by Replication. It is technically the Log Reader Agent job. It
used to be set to run every 15 minutes until I recently changed it to run
continuosly. The last_run_outcome from sp_help_job still says 0 which is
accurate as that was the last completed outcome. If Im not mistaken, until
the job actually stops again, that outcome will stay 0? Therefore either
using my query or sp_help_job will indicate failure.

Show quoteHide quote

"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
news:e07l81lbfcg3lct0ii9frtug8q52mjch1e@4ax.com...
> Take a look at sp_help_job. Or look at the sql for
> sp_help_job. It sounds like you may actually be looking for
> last_run_outcome which sp_help_job obtains from sysjobsteps.
>
> -Sue
>
> On Tue, 17 May 2005 13:26:56 -0700, "ChrisR"
> <noem***@bla.com> wrote:
>
>>I want to have a query that can tell me all the jobs on a server that have
>>failed and need to be run again in the future. This is what I have come up
>>with:
>>
>>select Server = 'BoxName', SJ.Name
>>from BoxName.msdb.dbo.SysJobHistory SJH
>>inner join BoxName.msdb.dbo.SysJobs SJ on SJH.job_id = SJ.job_id
>>inner join BoxName.msdb.dbo.SysJobSchedules SJS on SJ.Job_id = SJS.job_id
>>where SJH.run_status = 0
>>and SJS.enabled = 1
>>and SJS.next_run_date <> 0
>>and SJS.next_run_time <> 0
>>group by Server, SJ.Name
>>
>>
>>The problem though is it would appear that SQL doesn't update the
>>run_status
>>in the SysJobHistory table very often as I am getting values returned from
>>this query that did fail several hours ago, but have since succeeded. Does
>>anyone know of a better way to write this query? I know I can setup
>>Alerting, but need a backup for it.
>>
>>TIA, ChrisR
>>
>