sysjobhistory table

microsoft.public.sqlserver.programming

15 Sep 2005 3:45 PM

Sammy

I have a step in a job that hangs occasionally, but this step does not
show any errors but I can be certain if the job has not not reached a step
2 of the job in a certain amount of minutes it has failed.

But I have a problem in the sysjobhistory table there is a value (4) that
denotes if a job is running but it appears if a job is running it will not
record as running so what is the point of this???? my non-working script below

thanks for any help Sammy

f exists (select * from tempdb..sysobjects where name='##jobhist') drop
table ##jobhist

select step_id, step_name,run_duration, run_status,run_date into ##jobhist
from msdb..sysjobhistory

where job_id='98969218-B9BC-41C6-9836-C6D3A6A3DDDF'

and step_id =1

and run_date=convert(char(10), getdate(),112)

and run_status = 4

if exists (select * from ##jobhist where step_id=1)

begin

exec msdb..sp_stop_job @job_id='98969218-B9BC-41C6-9836-C6D3A6A3DDDF'

WAITFOR DELAY '0:05:00'

exec msdb..sp_start_job @job_id='98969218-B9BC-41C6-9836-C6D3A6A3DDDF'

WAITFOR DELAY '0:21:00'

if exists (select step_id, step_name,run_duration, run_status,run_date from
msdb..sysjobhistory

where job_id='98969218-B9BC-41C6-9836-C6D3A6A3DDDF'

and step_id =1

and run_date=convert(char(10), getdate(),112)

and run_status =4) -- if job is still running problem exists

begin

--the send msg to phones

exec master..xp_sendmail @recipients='SQLFailure', @message='Problem',

            @subject='Copy tables from Synergy taking to long check this',
@width=600

end

end

else

begin

            exec master..xp_sendmail @recipients='_dba', @message='Complete.',

            @subject='Copy tables from Synergy Successful', @width=600

end