February 9, 2011 at 11:23 am
Environment: Win2008\SQL 2008 SP2 Test server.
I have setup Database Mail, enabled it, and can do a test email and it sends the test email just fine. I created an Operator and then go into a job and go to the Notifications tab and select the correct operator to send. The job runs and the email is never sent. When I run the SP_HELP_OPERATOR it never shows any attempt to send the email. I look in the Database Mail errorlog and nothing shows up.
Is there anything I am missing?
February 9, 2011 at 11:31 am
Have you checked the db mail tables in msdb? I find looking in those tremendously helpful for oddball issues with that feature.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 9, 2011 at 11:36 am
Yes, nothing stands out as an error.
February 9, 2011 at 12:11 pm
have you "Enabled" the mail profile under the SQL Server Agent...
Open SSMS -->
Right click on SQL Server Agent -->
Choose Properties -->
choose Alert System -->
put a check mark on the Enable mail profile box.
February 9, 2011 at 12:15 pm
if you run this query, do you see the mail item that failed? if it's not failing, maybe a spam filter is intercepting it?
select * from msdb.dbo.sysmail_event_log lg
inner join msdb.dbo.sysmail_allitems ai
on ai.mailitem_id = lg.mailitem_id
where event_type='error'
in mine, i can find errors like "The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 92 (2011-01-04T12:00:36).
Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time,
or established connection failed because connected host has failed to respond 74.175.115.140:25). )"
Lowell
February 9, 2011 at 12:19 pm
Geoff A: Thanks... It was not enabled. I enabled it but still nothing.
I queried the table you listed and the table is empty.
February 9, 2011 at 12:53 pm
double check your sql server errorlog file !
I use these scripts to troubleshoot dbmail:
use msdb
go
/* is dbmail config activated ? */
Select *
from sys.configurations
where [name] = 'Database Mail XPs'
go
exec sysmail_help_queue_sp -- @queue_type = 'Mail'
go
select *
-- delete
from sysmail_event_log
order by log_date desc
select *
from sysmail_mailattachments
select *
from sysmail_allitems
order by sent_date
/* show failed stuff */
SELECT SEL.event_type
, SEL.log_date
, SEL.description
, SF.mailitem_id
, SF.recipients
, SF.copy_recipients
, SF.blind_copy_recipients
, SF.subject
, SF.body
, SF.sent_status
, SF.sent_date
FROM dbo.sysmail_faileditems AS SF
JOIN dbo.sysmail_event_log AS SEL
ON SF.mailitem_id = SEL.mailitem_id
/*
********** cleanup old messages **********
Declare @sent_before datetime
Select @sent_before = dateadd(MM,1,dateadd(dd,datediff(dd,0,getdate()),0))
print convert(varchar(26),@sent_before,121)
exec sysmail_delete_mailitems_sp @sent_before = @sent_before
, [ @sent_status = ] 'sent_status' ]
delete from sysmail_event_log where log_date < @sent_before
*/
/*
SELECT [account_id]
,[name]
,[description]
,[email_address]
,[display_name]
,[replyto_address]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_account]
SELECT [attachment_id]
,[mailitem_id]
,[filename]
,[filesize]
,[attachment]
,[last_mod_date]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_attachments]
SELECT [transfer_id]
,[uid]
,[filename]
,[filesize]
,[attachment]
,[create_date]
FROM [msdb].[dbo].[sysmail_attachments_transfer]
SELECT [mailitem_id]
,[profile_id]
,[recipients]
,[copy_recipients]
,[blind_copy_recipients]
,[subject]
,[body]
,[body_format]
,[importance]
,[sensitivity]
,[file_attachments]
,[attachment_encoding]
,[query]
,[execute_query_database]
,[attach_query_result_as_file]
,[query_result_header]
,[query_result_width]
,[query_result_separator]
,[exclude_query_output]
,[append_query_error]
,[send_request_date]
,[send_request_user]
,[sent_account_id]
,[sent_status]
,[sent_date]
,[last_mod_date]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_mailitems]
SELECT [profile_id]
,[principal_sid]
,[is_default]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_principalprofile]
SELECT [profile_id]
,[name]
,[description]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_profile]
SELECT [profile_id]
,[account_id]
,[sequence_number]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_profileaccount]
SELECT [uid]
,[text_data]
,[create_date]
FROM [msdb].[dbo].[sysmail_query_transfer]
SELECT [conversation_handle]
,[mailitem_id]
,[send_attempts]
,[last_send_attempt_date]
FROM [msdb].[dbo].[sysmail_send_retries]
SELECT [account_id]
,[servertype]
,[servername]
,[port]
,[username]
,[credential_id]
,[use_default_credentials]
,[enable_ssl]
,[flags]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_server]
SELECT [servertype]
,[is_incoming]
,[is_outgoing]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_servertype]
*/
/*
/********** cleanup old messages *********/
Declare @sent_before datetime
Select @sent_before = dateadd(MM,-1,dateadd(dd,datediff(dd,0,getdate()),0))
print convert(varchar(26),@sent_before,121)
exec msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @sent_before
delete
from dbo.sysmail_event_log
where log_date < @sent_before;
*/
Select convert(xml,' Troubleshooting Database Mail on Windows Server 2008 and 2008 R2 : Also have a look at http://sqlblog.com/blogs/john_paul_cook/archive/2010/07/04/troubleshooting-database-mail-on-windows-server-2008-and-2008-r2.aspx') as Remark
another ref:
Troubleshooting Database Mail on Windows Server 2008 and 2008 R2 : Also have a look at http://sqlblog.com/blogs/john_paul_cook/archive/2010/07/04/troubleshooting-database-mail-on-windows-server-2008-and-2008-r2.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 10, 2011 at 6:19 am
I found the answer on another forum.... You have to stop and start SQL Server Agent. Mail now sends!
February 10, 2011 at 8:29 am
ah, indeed, that is essential.
thank you for the feedback.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply