Send Database Mail only once

Question

Send Database Mail only once

henrikf

Grasshopper

Points: 12
More actions
April 14, 2015 at 3:09 am

#295980

I have a job under the SQL Server Agent, which is configured to send an email notification on failure.
The job is scheduled to run every 30 minutes.
Is it somehow possible to configure it so that it only sends one email in case of subsequent failures instead of "spamming" my inbox every half hour??
I hope you understand, please excuse English is not my native language...

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

It's not possible directly. You have some options:

1) Create another job (B) that checks the outcome of the first job (A) every 30 minutes and sends and email only when the the failure is the latest one (query sysjobhistory for this)

2) Add a step in the job to check outcome from previous executions and send a notification only when no previous failures exists (again, sysjobhistory is the table to query)

-- Gianluca Sartori

henrikf Grasshopper Points: 12 More actions · Answer 2

Thank you, I will look into option two - seems the most elegant way is to have it incorporated in the same job.

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 3

Heh... as a bit of a sidebar, when there's a failure, I "SPAM" folks every 2 minutes to establish a sense of urgency. 😀

--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)

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Jeff Moden (4/14/2015)
Heh... as a bit of a sidebar, when there's a failure, I "SPAM" folks every 2 minutes to establish a sense of urgency. 😀

Pfft! Amateur. Somebody once told me he brought the corporate Exchange down with hundreds of thousands of emails from a SQL Agent alert with no "delay between responses" set. 😀

I think it was Jonathan Allen, but I'm not sure.

-- Gianluca Sartori