July 30, 2012 at 9:39 am
Hi, I've created a Database Mail account (tested and sends with no problem), created an operator, set up an alert on the job I want to monitor, and restarted my SQL Server Agent. But I still am not alerted when the job in question completes. Is there a step that is missing?
July 30, 2012 at 10:02 am
How is the alert specified exactly? It is specified for success, rather than failure isn't it?
July 30, 2012 at 10:03 am
I've tried success, failure, and completion (with corresponding T-SQL script that completes or errors out). No e-mail sends on any of them
July 30, 2012 at 10:19 am
Did you enable the mail profile under the SQL Server Agent Alert System properties?
July 30, 2012 at 10:46 am
The first thing I would try to identify is where the failure is happening. I know you stated your test message works (it usually does).
Is SQL Server failing to send the mail or is your mail server rejecting it? Or some other failure?
Check the following system tables for the mail messages:
sysmail_allitems, sysmail_sentitems, sysmail_unsentitems, sysmail_faileditems
In sysmail_mailitems, sent_status=1 means success, sent_status=2 failure, sent_status=pending (unsent).
July 30, 2012 at 11:40 am
Hi, mail profile is enabled for SQL Server Agent. The test e-mails I've sent are present in the system mail tables. The e-mails that are supposed to be sent (for the actual jobs) are not present
July 30, 2012 at 12:58 pm
guerillaunit (7/30/2012)
Hi, mail profile is enabled for SQL Server Agent. The test e-mails I've sent are present in the system mail tables. The e-mails that are supposed to be sent (for the actual jobs) are not present
Then it sounds to me like the problem has to be in either of the following: The SQL Agent job itself, the Notification configuration or the Alerts.
I have a test server with something similar but it's running SQL 2005. As your question pertains to 2008 I'm not sure my set up will match yours exactly but here goes...
Database Mail enabled, profile and operator both configured. On the Notifications tab of the Job Properties, I have E-mail checked and the Operator selected from the drop down. Next to that I have "when the job completes" selected.
I do not have anything special configured under the Alerts. Either on the tab on the job or on the Alerts sub-section of the SQL Server Agent. Hope that helps.
July 30, 2012 at 1:08 pm
Is the mail profile you created set as the default mail profile?
If not, try setting it as the default.
If it is, or if you don't want to set a default profile, then try explicitly setting the profile as the one the agent will use.
In SSMS, Right-click on SQL Server Agent and select properties.
Select Alert System.
Check the "Enable mail profile" box, select Database Mail in the Mail System dropdown, and select your profile name in the Mail profile dropdown.
I've found on my systems that if a profile has ever been set for the agent, turning it off again it set it back to using the default profile for some reason. I have to explicitly set the default profile as above.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply