asked on
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_alli
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!
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_alli
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!
Microsoft SQL ServerMiscellaneousSQL
Last Comment
waqqas31
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.
ASKER
@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?
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?
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.
I would also try to include the word "backup" into the "Message Text" as seen in screen shot below:
![Untitled.png]()
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/
https://www.mssqltips.com/sqlservertip/939/sql-server-database-backup-and-restore-failure-notifications/
ASKER
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!
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!
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.