SQL job sending email even if not configured to send notifications

All we need is an easy explanation of the problem, so here it is.

I am using this version of SQL Server.

Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64)   Feb 15 2020 01:47:30   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) 

This might sound counter-intuitive but I want some of my jobs not to send an email when they fail.

I have a very busy Server and some of the jobs fails when they get into a deadlock with other processes. I’m fine with this, as long as most of the time the job succeeds.

I was having too much emails of job failure, so instead, I made myself a report that gives me the jobs that failed yesterday, and the number of occurrences that they failed.

SET QUOTED_IDENTIFIER ON;

declare @Today date, @Yesterday varchar(8)

select @Today = GETDATE()
select @Yesterday = REPLACE(CONVERT(VARCHAR(10),DATEADD(day, -1, @Today),121),'-','')


;With failedJobs as 
(
select job_id, step_id, COUNT(*) as Occurrences 
from msdb.dbo.sysjobhistory 
where run_date>[email protected] and run_status=0
group by job_id, step_id
)
SELECT j.name,fj.Occurrences,MAX(fj.step_id) as LastSteap
from msdb.dbo.sysjobs j
join failedJobs fj on fj.job_id=j.job_id
group by j.name, fj.Occurrences

My problem is that even though I unchecked the Notification in SSMS, I’m still receiving emails when the job deadlocks.

SQL job sending email even if not configured to send notifications

Any idea what I need to disable? Do I need to disable the failsafe operator? I would like to avoid that if possible.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Similar to this article, you may have separate alerts set up for Error 1205, a deadlock. Expand the alert folder under the SQL Server Agent, and see if there are any there.

If not, and you’re actually getting messages about all job failures (not clear from question if deadlock specific), you can test this by creating a new job, _Test that simply does something designed to fail, like SELECT 1/0. Run it, and see if you’re somehow getting a job failure message from something else. If you do, then you have to go looking for what that thing is.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply