locked
Receiving historical SQL alerts when SQL Server agent is restarted RRS feed

  • Question

  • I'm facing this issue for SQL server 2014 that 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. Thank you.

    Regards, CJ


    • Edited by Cj-DBA Wednesday, October 14, 2015 12:50 PM
    Wednesday, October 14, 2015 12:49 PM

Answers

  • 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


    Thursday, October 29, 2015 6:10 AM
  • Hi CJ,

    Generally, restart SQL Server or SQL Agent will not trigger the alerts. You can verify this by checking the History of the given alert, see if it has been triggered right after the restart.

    And then, try check the execution history of the job which is associate with the alert to send those emails, verify if the job has been executed right after the restart.

    If it has been executed, then we need to find out why.

    Also, check the Job Activity Monitor, check if there are other job has been executed that might send those emails.

    Monday, October 26, 2015 8:55 AM

All replies

  • put your required date following query.it will delete dat.

    EXEC  msdb.dbo.sysmail_delete_mailitems_sp

          @sent_before = '2014-05-10 00:00:00',

          @sent_status = 'sent'



    Please click "Mark As Answer" if my post helped.

    Friday, October 16, 2015 4:22 AM
  • Hi ,

    Thank you for your question.

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.

    Thank you for your understanding and support.

    Regards,

    Ice fan


    Ice Fan
    TechNet Community Support




    • Edited by Ice Fan Friday, October 16, 2015 7:06 AM
    Friday, October 16, 2015 7:05 AM
  • Hi CJ,

    Generally, restart SQL Server or SQL Agent will not trigger the alerts. You can verify this by checking the History of the given alert, see if it has been triggered right after the restart.

    And then, try check the execution history of the job which is associate with the alert to send those emails, verify if the job has been executed right after the restart.

    If it has been executed, then we need to find out why.

    Also, check the Job Activity Monitor, check if there are other job has been executed that might send those emails.

    Monday, October 26, 2015 8:55 AM
  • 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


    Thursday, October 29, 2015 6:10 AM
  • Hi CJ,

    Did you ever find a solution to this issue?  

    Microsoft provided the below fix

    https://support.microsoft.com/en-au/help/4052127/fix-alert-engine-sends-alerts-on-old-events-after-windows-restart

    Tuesday, May 8, 2018 12:14 AM