How do you use cloud databases? Take the survey.

Alert fired, but no email notification

SQL Monitor 10.0.7.27595

We have email notifications configured for SQL Agent job failures.  Last night we had two different jobs fail on the same SQL instance, which triggered two alerts, but only one email notification came through.  When looking at the actual alerts that fired, I don't see anything that tells me if a notification was sent or not.  Am I missing something that would tell me that an alert sent an email?  Please help me troubleshoot this.

Thank you,

-Peter
Tagged:

Answers

  • Russell DRussell D Posts: 1,324 Diamond 5
    edited May 15, 2020 8:59AM
    Hi Peter, you can look in the alert.notifications table to see if the alert was ever fired, joining across to the alert table on the alert id. It might be best to get in contact with Support directly. Please remember to attach the log files.
    Have you visited our Help Centre?
  • PeterDanielsCRBPeterDanielsCRB Posts: 126 Bronze 3
    edited May 15, 2020 2:37PM
    Thanks, @Russell D.  I wrote the following SQL based on your guidelines:

    SELECT TOP(10)
    	at.Name AS AlertTypeName
    	,a.AlertId
    	,a.TargetObject
    	,a.Raised
    	,n.NotificationDate
    	,n.NotificationType
    FROM
    	alert.Alert AS a
    	INNER JOIN alert.Alert_Type AS at
    		ON(a.AlertType = at.AlertType)
    	LEFT JOIN alert.Notifications AS n
    		ON(a.AlertId = n.AlertId)
    	--LEFT JOIN alert.
    WHERE
    	at.Name = N'Job failing'
    ORDER BY
    	a.AlertId DESC
    I'm assuming that NULL for NotificatonDate and NotificationType means no notification was sent.  Before submitting a support ticket wondering if you could help me navigate the myriad log files.  Where would I look for issue related to notifications not being sent?

    Thanks,

    -Peter

    PS Any tips on parsing/interpreting your "fancy" Date column values (e.g. 637249573070000000)?
  • Russell DRussell D Posts: 1,324 Diamond 5
    edited May 18, 2020 9:01AM
    Hi Peter, these are in ticks. There's a function included in the database TicksToDateTime, can be called with selecting into, eg

    <p>select [AlertId]</p><p>,[AlertType]&nbsp;&nbsp;&nbsp;&nbsp;</p><p>,[TargetObject]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</p><p>,[Read]&nbsp;&nbsp;&nbsp;&nbsp;</p><p>,utils.TicksToDateTime([ReadDate]) as 'Read Date'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</p><p>,[SubType]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</p><p>,[LastUpdate]&nbsp;&nbsp;&nbsp;&nbsp;</p><p>,utils.TicksToDateTime([Raised]) as 'Raised Date'&nbsp;&nbsp;&nbsp;&nbsp;</p><p>,[LastSeverity]&nbsp;&nbsp;&nbsp;&nbsp;</p><p>,[WorstSeverity]&nbsp;&nbsp;&nbsp;</p><p>,[Cleared]</p><p>,utils.TicksToDateTime([ClearedDate]) as 'Cleared Date'</p><p>&nbsp;,[LastComment]</p><p>,[RootCir]</p><p>,[GroupCir]</p><p>from [alert].[Alert]</p><p>go</p>

    hm sorry for the formatting, I've no idea why the code blocks are like that.
    Have you visited our Help Centre?
  • PeterDanielsCRBPeterDanielsCRB Posts: 126 Bronze 3
    Thanks, @Russell D , for the fn tip (TicksToDateTime).  Is this in the docs anywhere?  Also, wondering if you might have a follow-up to my request for which logs to look at (and what to look for in them) for failed email notification.  Here is what I see from my query:


  • Russell DRussell D Posts: 1,324 Diamond 5
    It's not documented because we don't really Support querying the database directly (though you are free to do so). If there's a null in the Notification date then a Notification was never sent, even if the email was sent but failed to get to its destination, there would be a time in this field.

    You can look in the basemonitor.log for email failures.
    Have you visited our Help Centre?
  • PeterDanielsCRBPeterDanielsCRB Posts: 126 Bronze 3
    Thanks, @Russell D . That makes sense (not documented due to not supporting direct querying of the DB).

    I looked at "base monitor.log" file (found it in .\localhost\BaseMonitor).  Didn't see anything regarding notification/email failure during the time frame when this occurred.  Might be time for a support case.
  • Russell DRussell D Posts: 1,324 Diamond 5
    I think so, just fire the logs in to Support and we can check it out after they've picked it up.
    Have you visited our Help Centre?
Sign In or Register to comment.