SQL job sending email even if not configured to send notifications

Question

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>=@Yesterday 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.

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

Answer 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.