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.
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 :
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 🙂