0

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.

ssms

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

1
  • 1
    Are any of the jobs replication or other SQL Server out-of-box feature related by any chance? What does the Alerts property screen look like?
    – J.D.
    Jun 17 at 18:22
3

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.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.