March 15, 2012 at 10:22 am
When it comes to Database Mail I pretty much install and configure SQL servers in pretty much the same manner. I configure the db mail to email via exchange, create an operator, confirm the test email works, create a default profile, etc. I also have a standard set of jobs that send periodic emails to the group, and upon failure, it will email the group when/why the job failed.
When I query sysmail, I can see emails go out, but none from when errors in the SQL Agent job get fired:SELECT recipients, blind_copy_recipients, subject, file_attachments, send_request_date
FROM msdb..sysmail_mailitems with(nolock)
where send_request_date >= '2012-03-11 00:52:00.780'
dba@mail.comNAS2-ONBASESQL: STATS :: 2012-03-11 07:45:00.767NULL2012-03-11 07:45:01.457
ia@mail.com;ONBASE - Import Merchants Missing - Mar 11 2012 9:36AMNULL2012-03-11 09:36:15.023
ia@mail.com;ONBASE - Import Fleets Missing - Mar 11 2012 9:36AMNULL2012-03-11 09:36:15.127
dba@mail.comNAS2-ONBASESQL: STATS :: 2012-03-12 07:45:00.777NULL2012-03-12 07:45:02.050
dba@mail.comNAS2-ONBASESQL: STATS :: 2012-03-13 07:45:00.827NULL2012-03-13 07:45:02.863
dba@mail.comNAS2-ONBASESQL: STATS :: 2012-03-14 07:45:00.770NULL2012-03-14 07:45:03.660
dba@mail.comNAS2-ONBASESQL: STATS :: 2012-03-15 07:45:00.653NULL2012-03-15 07:45:02.647
me@mail.comDatabase Mail TestNULL2012-03-15 10:19:29.330
ia@mail.com;ONBASE - Import Merchants Missing - Mar 15 2012 10:45AMNULL2012-03-15 10:45:01.517
ia@mail.com;ONBASE - Import Fleets Missing - Mar 15 2012 10:45AMNULL2012-03-15 10:45:01.630
I am getting regular emails from the server but am not receiving any of the emails for when the jobs fail - does anyone have any ideas? Attached is an image of how the SQL Agent job is set up for notifications...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 15, 2012 at 10:40 am
Did u create an alert as well and checked the option e-mail to the operator DBA ?
March 15, 2012 at 10:48 am
I did not create a specific alert (as I don't believe it is needed to trigger the email for a job failure). I have not done this on any other server...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 15, 2012 at 10:55 am
You have to set a notification on the job, not an alert. It will not do any emails by default.
Jared
CE - Microsoft
March 15, 2012 at 11:00 am
As you can see from the image attachment, this was done initially (again I set db mail and the jobs up the same way on all my servers (+20)). This is the only one that does not send out an emails when a job fails. I do receive emails from this machine....just not when there's an error
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 15, 2012 at 11:05 am
MyDoggieJessie (3/15/2012)
As you can see from the image attachment, this was done initially (again I set db mail and the jobs up the same way on all my servers (+20)). This is the only one that does not send out an emails when a job fails. I do receive emails from this machine....just not when there's an error
Sorry... Not reading slow enough 🙂 Are you sure that the job is actually failing? I have seen cases where the job "completes" bit there was an error in the execution; ex. Job runs local SP, local SP calls remote SP, SP fails remotely but job still "completes." I assume you have looked at the job history?
Jared
CE - Microsoft
March 15, 2012 at 11:07 am
Yes of course, legitimate failure...ghost box :w00t:
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 15, 2012 at 1:33 pm
has SQL agent been restarted after it was configured to use the mail profile in the SQL agent alert settings?
March 15, 2012 at 2:12 pm
Yes. i did finally find an error mesage:
Date3/15/2012 3:10:52 PM
LogSQL Server Agent (Current - 3/15/2012 3:05:00 PM)
Message
[264] An attempt was made to send an email when no email session has been established
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 15, 2012 at 2:17 pm
This script returns nothing...which I would have expected to have had "some" rows in it...
SELECT
err.[description],
fail.*
FROM [msdb].[dbo].[sysmail_event_log] err
inner join [msdb].[dbo].[sysmail_faileditems] fail
ON err.mailitem_id = fail.mailitem_id
This is all so very weird...I have a job that will email me any job-step failures on any given SQL server...I didn't get an email from the job directly when the job step failed, but I did get the email letting me know that a job had failed???
-----Original Message-----
From: Server Notifier [mailto:server@mycompany.com]
Sent: Thursday, March 15, 2012 3:15 PM
To: DBA_Alerts
Subject: Job Step Failures Detected 2012-03-15 15:15:00
ERROR #1 - test on Server (executes select 1/0)
RUN DATE: 2012-03-15 Run Time: 15:10:51
=======================================
Step: 1 - test
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply