2

The issue is faced in SQL server 2014, whenever SQL Server Agent is restarted or if the server reboots, there are historical SQL alerts which are received and emails are being sent to the various DLs.

The SQL alerts are of the various errors received and alerts configured for the same. They are as old as from 2014.

I’ve troubleshoot the issue further and found that the most old historical alert we received is for 8/28/2014 9:16:36 AM. Checking the SQL error logs, the last date/time stamp of event is 11/30/2014 12:00:01 AM, and checking the Windows Application events logs we can see that the last event is of 8/27/2014 3:00:51 PM. Hence based on the information at the Microsoft site (SQL Server Agent reads the application log and compares events written there to alerts that you have defined. When SQL Server Agent finds a match, it fires an alert, which is an automated response to an event.) I’m assuming that in our case as well the historical alerts are being fired as there are these events in the Windows Events logs.

However, I still trying to figure why alerts of this particular day are being sent. As in, we are getting a historical alert for failed backup of ‘******’ on 9/14/2014 3:11:50 PM and the same entry is present in the Windows Application Event logs as well, the same event has occurred on 10/5/2014 3:14:34 PM and we are not receiving the historical alert for this event.

I’ve checked the table ‘sysmail_allitems’ and other related tables in msdb database and found that there are logs of the emails triggered in 2013 also but we are not receiving historical alerts of those events, hence there is a possibility that we are receiving historical alerts as those events present in the Windows Application Event logs.

I have also checked the mail queue and found them to be empty.

EXEC msdb.dbo.sysmail_help_queue_sp

Please let me know if i'm troubleshooting in the wrong direction. Also let me know if you need any more information.

  • Check the Internet message headers from the email and confirm the IP address where the email is coming from is the system which you are expecting these email notifications to come from just in case. I'm also not sure but perhaps backing up and then letting new application event viewer logs and SQL error logs may be a quick solution to ensure the matching old events aren't triggered would suffice -- meaning back them up and then purge perhaps? You might check this as well...msdn.microsoft.com/en-us/library/ms177285(v=sql.120).aspx – Pimp Juice IT Oct 14 '15 at 14:35
  • Hi All, The issue seems to be resolved after clearing the Event viewer logs. Though there is a possibility of old alerts maybe received again as the events in the Windows Event logs get populated. As of now this seems to be a workaround but not a fix to this problem. Will keep you posted if the issue occurs again. Thank you all for your time and help. Regards, CJ – user5435714 Oct 29 '15 at 6:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.