Alert fired, but no email notification

PeterDanielsCRB

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

Russell D

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.

PeterDanielsCRB

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 D

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.

PeterDanielsCRB

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 D

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.

PeterDanielsCRB

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 D

I think so, just fire the logs in to Support and we can check it out after they've picked it up.