Avatar of waqqas31
 asked on

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


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!
Microsoft SQL ServerMiscellaneousSQL

Avatar of undefined
Last Comment

8/22/2022 - Mon

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.


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.
Your help has saved me hundreds of hours of internet surfing.

I would also try to include the word "backup" into the "Message Text" as seen in screen shot below:

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.


Thanks for that link to configure alerts for the error numbers.  It led me to find another article on the same website:


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!
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.

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.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.