Some SQL Server Agent alerts are getting missed completely by Sysmail / DBmail

Hello,

I have configured SQL Server Agent alerts to send emails when they are raised, among them is a "SQL Server event alert" for Severity "016 - Miscellaneous User Error".

I have set the "Response" of the alert to email two operators, and in "Options" I have selected to include alert error text in "E-mail".

The problem is that this alert is working for the most part.

I run scheduled differential backups overnight for 51 databases and usually a handful will fail due to insufficient disk space on the backup server (error 112).  If there are 5 such failures, I will receive anywhere from 3-5 emails, with no predictability.

I questioned if it was my email server which was not working 100%, but after querying [msdb].[dbo].[sysmail_allitems] there is actually no mention of the missing alert email.  The ones I did receive are there, but not the missing one.  It is also not there in [msdb].[dbo].[sysmail_faileditems] or any of the other sysmail views, for that matter.

However, in the Windows Event Viewer, the "missing" alert and all the alerts for "Severity 016" are there like they should be.

I am running SQL Server 2014 Developer, build 12.0.5589.7 (SP2 CU12).

Any help will be greatly appreciated!
waqqas31Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
Please have a look also at msdb..sysmail_unsentitems and msdb..sysmail_sentitems as they may help you to determine the cause for mail not being received as expected. I would also add a notification to the actual SQL Job that does the backup to send me an email alert on failure and that should explicitly include the reason of failure like not sufficient disk space.
waqqas31Author Commented:
@Icohan

Thanks, but as I mentioned, none of the msdb.dbo.sysmail views have any mention of the missed email alerts.

I do run the backups using Maintenance Plans and I am alerted when a particular backup fails, however, I'm still working on how to capture the error message and email that to an operator.  Would you have any pointers for figuring that part out?
lcohanDatabase AnalystCommented:
If you open SSMS connect to that server under the SQL Jobs you should have listed all those maintenance plans and if you right click on one of them under "Job Properties" left Pane select Notifications and in there you can check-box Email and enter a operator email to send an email "When the job fails" for instance.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

lcohanDatabase AnalystCommented:
I would also try to include the word "backup" into the "Message Text" as seen in screen shot below:
Untitled.png
lcohanDatabase AnalystCommented:
You could also add a specific alert and use the actual error number from list at link below instead of the generic "016 Miscellaneous..." as I believe this may give more accurate response as expected on the specific error code.When you create the new alert - check the "Error number" and select appropriate from the list then go from there.

https://www.mssqltips.com/sqlservertip/939/sql-server-database-backup-and-restore-failure-notifications/
waqqas31Author Commented:
Thanks for that link to configure alerts for the error numbers.  It led me to find another article on the same website:

https://www.mssqltips.com/sqlservertip/4174/suppressing-sql-server-alerts-to-reduce-emails/

Which, I believe, might be the root cause of my issue, namely the "Delay between responses" setting.  I was under the impression that this setting restricts the frequency with which the DBmail queue sends an email, but it is actually a time interval which suppresses all but the first alert.

I am going to let the backups run tonight with the modified setting and report back tomorrow on whether it was successful or not.

Thanks for your help so far!
waqqas31Author Commented:
The solution was to set the "Delay between reponses" to 0 minutes and 0 seconds.  

https://www.mssqltips.com/sqlservertip/4174/suppressing-sql-server-alerts-to-reduce-emails/

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.