Job fails but Job History shows success

Question

Job fails but Job History shows success

triloba

Mr or Mrs. 500

Points: 582
More actions
January 17, 2019 at 12:36 pm

#370249

We had an undetermined network issue last night. Multiple database backup jobs (full & Trans log) failed. Many also ran successfully. I got notification emails for the failed jobs, but if I look at the Job History for those, it shows success (both the job and the one step). The backup files don't exist, so the jobs failed.
Is there a problem with the Job History? I'm looking through SSMS V 17.9.1 at individual jobs running on SQL Server 2014 Standard.
the notifications are set to email me on failure.
These jobs have been running for months, if not years.
I've since run most (full backups) manually and did not get notifications.
Thanks,
Tom

Viewing 5 posts - 1 through 4 (of 4 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

triloba - Thursday, January 17, 2019 12:36 PM
We had an undetermined network issue last night. Multiple database backup jobs (full & Trans log) failed. Many also ran successfully. I got notification emails for the failed jobs, but if I look at the Job History for those, it shows success (both the job and the one step). The backup files don't exist, so the jobs failed.
Is there a problem with the Job History? I'm looking through SSMS V 17.9.1 at individual jobs running on SQL Server 2014 Standard.
the notifications are set to email me on failure.
These jobs have been running for months, if not years.
I've since run most (full backups) manually and did not get notifications.
Thanks,
Tom

Not aware of any huge bugs like that with Job History. Did you look at the job that ran at the time of the failure and not necessarily just the last run? If you got the email for the failure and the job is set to only notify you of the failure then it was seen as a failed job. That's why I'm wondering about the times and if the job has run a bunch of times, are you looking at the run where it reported failure.
The other cases where something like that may happen depends on how you are executing the backups in the jobs. Running maintenance plans, using Powershell for the backups, calling other jobs, etc can all make a difference.

Sue

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

triloba - Thursday, January 17, 2019 12:36 PM
We had an undetermined network issue last night. Multiple database backup jobs (full & Trans log) failed. Many also ran successfully. I got notification emails for the failed jobs, but if I look at the Job History for those, it shows success (both the job and the one step). The backup files don't exist, so the jobs failed.
Is there a problem with the Job History? I'm looking through SSMS V 17.9.1 at individual jobs running on SQL Server 2014 Standard.
the notifications are set to email me on failure.
These jobs have been running for months, if not years.
I've since run most (full backups) manually and did not get notifications.
Thanks,
Tom

The code was probably written to continue to backup what it can despite the fact that 1 or more individual backups failed. That means that the job succeeded in doing what it was designed to do and SQL Server reports that fact as a success. If you want notification of failures or some other indication, have the job send you a log or notify you immediately (even though it continues) if any give backup fails.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

triloba Mr or Mrs. 500 Points: 582 More actions · Answer 3

Thanks Sue & Jeff for the suggestions. Jeff comes the closest.
The backup jobs call one stored proc that backs up the database (db name passed in as parameter). So, the job ran the stored proc correctly. The stored proc runs successfully and the catch block catches the error.

Thanks
Tom

Sue_H SSC Guru Points: 90860 More actions · Answer 4

triloba - Friday, January 18, 2019 5:55 AM
Thanks Sue & Jeff for the suggestions. Jeff comes the closest.
The backup jobs call one stored proc that backs up the database (db name passed in as parameter). So, the job ran the stored proc correctly. The stored proc runs successfully and the catch block catches the error.
Thanks
Tom

Thanks for posting back - that what I was wondering about running it in Powershell, maintentance plan, etc. Sorry - should have include stored procedure in that.
But it is interesting that you got an email that the job failed when it succeeded though.
Sounds like it wasn't a notification for the job failing but maybe a notification in your stored procedure.

Sue