Process to Prevent Email Spam

Question

Process to Prevent Email Spam

amy26

SSCrazy Eights

Points: 8926
More actions
May 24, 2017 at 7:43 am

#337996

I am wondering if anybody has set up any sort of trigger to turn off database mail in the event of a glitch or a preventative measure to ensure infinite number of emails do not get queued up in the system? We recently had a glitch in code (that was ironically tested) that generated over 4 million emails in a matter of a few hours and spammed the heck out of one person. By the time I caught it (it had been running overnight) 120,000 emails were successfully sent to the same person and it had queued up about 4 million.
I'm wondering if I can create a trigger on the sysmail_allitems table to turn off dbmail and send an administrative email if it detects the presence of "spam" in the table. How I define the term "spam" is in question... but that was why I was wondering if anybody had already developed something I might be able to tweak? Or if this is a good idea?
Thoughts welcome.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

you could create an INSERT trigger on the sysmail_log table, to evaluate the count of rows in the last minute, and if more than example 20, send mail to admin.
This will return a row count for last 10 minutes, but keep in mind, one email != one row.

select count(*) from msdb.dbo.sysmail_log where datediff(minute, log_date, getdate()) < 10

then execute this:

USE msdb GO EXECUTE dbo.sysmail_stop_sp GO

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

amy26 SSCrazy Eights Points: 8926 More actions · Answer 2

Thank you!

On the log table though? I know the log just shows starting and stopping of the service... does it generate start and stop messages for every email it sends? I wouldn't put it on the mail items table instead?

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 3

amy26 - Wednesday, May 24, 2017 8:09 AM
I wouldn't put it on the mail items table instead?

Yes you can, it might be better.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle