Step fails, but job reports success.

Question

Step fails, but job reports success.

Erin.

SSCertifiable

Points: 5237
More actions
September 4, 2009 at 6:22 am

#156178

I have a job that has multiple steps. Each step is set to 'Go to next step' on success or failure, except for the last step which is set to 'Quit the job reporting success/failure'. If the last step fails I get an email notification. If a step other than the last step fails I do not receive an email notification.
I am looking for a way to monitor jobs so I am aware when something like this happens. As anyone run into this? Any suggestions?
I am starting to look at the msdb database tables. I am thinking of setting up a Reporting Services report that queries these tables and emails me the results daily.
Does anyone have any ideas on how I could receive a failure notification instantly? Maybe a trigger? I have very little experience with triggers.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 1

Maybe on failure you should use "Quit the job reporting success" ? Or maybe you can make a query against msdb.dbo.sysjobhistory ?

Animal Magic SSChampion Points: 13964 More actions · Answer 2

Do you still actually want the job to continue if one of the steps fail? if not then just change the failure action to "quit the job reporting failure". Assuming you do want the job to continue then you would have to start querying the system tables to retreive the step status'.

Erin. SSCertifiable Points: 5237 More actions · Answer 3

I am using the following trigger I found on SQL Server Central.

USE [msdb]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

----------------------------------------------------------------------------

-- Object Type : Trigger

-- Object Name : msdb..trg_stepfailures

-- Description : Send notifications on job STEP failures (trigger)

-- Author : http://www.sqlservercentral.com By Kenneth Singer

-- Date : August 2009

----------------------------------------------------------------------------

CREATE trigger [dbo].[trg_stepfailures]

on [dbo].[sysjobhistory]

for insert

as

declare @strMsg varchar(400),

@strRecipient varchar(128),

@copyRecipient varchar(128),

@bccRecipient varchar(128)

set @strRecipient = 'recipient@company.com'

set @copyRecipient = 'recipient@company.com'

set @bccRecipient = ''recipient@company.com'

if exists (select * from inserted where run_status = 0 and step_name '(job outcome)')

begin

select @strMsg =

convert(char(10),'Server') + char(58) + @@servername +

char(10) +

convert(char(10),'Job') + char(58) + convert(varchar(50), sysjobs.name) +

char(10) +

convert(char(10),'Step') + char(58) + convert(varchar(50), inserted.step_name)+

char(10) +

convert(char(10),'Message') + char(58) + convert(varchar(150), inserted.message)

frominserted

joinsysjobs

oninserted.job_id = sysjobs.job_id

whereinserted.run_status = 0

raiserror (@strMsg, 16, 10) with log

exec msdb.dbo.sp_send_dbmail

@recipients = @strRecipient,

@copy_recipients = @copyRecipient,

@blind_copy_recipients = @bccRecipient,

@body = @strMsg,

@subject = 'Job Failure'

end

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 4

I had this once where it would remain as Red x that it didn't run but it had run. I ended up just adding a new job and deleting it in the end...

kdslate Newbie Points: 1 More actions · Answer 5

We added a step that emails on failure. That way, you can set the "On Failure" for each step to go to the Email step. Should the other steps succeed, have the last step (the one prior to the Email on Failure) set to Quit reporting Success for the OnSuccess action and the OnFailure action will still go to the Email on Failure step.

For the Email on Failure step, set the OnSuccess action to Quit reporting Failure. This will show the job as failed when it completes.

The job history will indicate which step failed as well.