October 13, 2010 at 4:11 am
Hi all,
Somehow, on my SQL Server 2008 mail stopped working. To fix this, I already tried the next things:
- Stop/start SQL instance
- Remove/create database mail profile (public!) and account
- Checked under "Facets"-"Surface Area Configuration" if DatabaseMaiolEnabled=True (it is)
- Also with sp_CONFIGURE (run_value Database Mail XPs=1)
- As I am sysadmin, I should not explicitly be a member of the DatabaseMailUserRole, but you never know.
So I am now.
- I confirmed that the Database Mail activation is started with:
EXEC msdb.dbo.sysmail_help_status_sp;
It is STARTED...
- I even stopped/started Database Mail activation
- I checked to see if the Database Mail External Program is started with:
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';
!!!State is INACTIVE!!! According to Microsoft this should have the state RECEIVES_OCCURRING
- A stop/start of sysmail did not help
- Checked if the Service Broker is enabled (it is)
- Checked the mailserver for possible blockings etc (no problem with the mailserver)
The emails are in unsentitems right now. Has anyone got any other idea's?
I feel like I am running out of options, except restarting the whole server.
Regards
October 13, 2010 at 5:12 am
you can check two things that I have had to fix when coming into a new project where DB Mail is not working. (always because it is misconfigured)
1. Verify that your existing account ( all of them ) have a single email address is the Email address field.
2. Verify that your existing profile (the one you are trying to use) is set to be the default public profile. (unless this conflicts with other settings...)
I have found that the best way to use DB Mail (for me anyway) is to have a single profile with a single account. I understand that mutiple profiles and multiple accounts are needed at some businesses, but I like to keep it simple.
October 13, 2010 at 5:22 am
Thanks, but that's well configured (like you are saying)
October 13, 2010 at 5:50 am
what is the output of the Database Mail Log?
October 13, 2010 at 6:03 am
10/13/2010 12:05:29,,Information,95,The mail queue was started by login "SQL_MEDICAL\stakes".,,,,10/13/2010 12:05:29 PM,SQL_SERVER\stakes
10/13/2010 12:05:27,,Information,94,The mail queue stopped by login "SQL_MEDICAL\stakes".,,,,10/13/2010 12:05:27 PM,SQL_SERVER\stakes
10/13/2010 12:05:23,,Information,93,The mail queue was started by login "SQL_MEDICAL\stakes".,,,,10/13/2010 12:05:23 PM,SQL_SERVER\stakes
10/13/2010 12:03:16,,Information,92,The mail queue was started by login "SQL_MEDICAL\stakes".,,,,10/13/2010 12:03:16 PM,SQL_SERVER\stakes
10/13/2010 12:03:12,,Information,91,The mail queue stopped by login "SQL_MEDICAL\stakes".,,,,10/13/2010 12:03:12 PM,SQL_SERVER\stakes
10/13/2010 12:02:16,,Information,90,The mail queue was started by login "SQL_MEDICAL\stakes".,,,,10/13/2010 12:02:16 PM,SQL_SERVER\stakes
10/13/2010 09:03:50,,Information,89,The mail queue was started by login "SQL_MEDICAL\stakes".,,,,10/13/2010 9:03:50 AM,SQL_SERVER\stakes
10/13/2010 09:02:54,,Information,88,The mail queue stopped by login "SQL_MEDICAL\stakes".,,,,10/13/2010 9:02:54 AM,SQL_SERVER\stakes
10/13/2010 09:02:54,,Information,87,The mail queue was started by login "SQL_MEDICAL\stakes".,,,,10/13/2010 9:02:54 AM,SQL_SERVER\stakes
10/12/2010 14:43:51,,Information,86,The mail queue was started by login "SQL_MEDICAL\stakes".,,,,10/12/2010 2:43:51 PM,SQL_SERVER\stakes
10/12/2010 14:43:41,,Information,85,The mail queue stopped by login "SQL_MEDICAL\stakes".,,,,10/12/2010 2:43:41 PM,SQL_SERVER\stakes
10/12/2010 13:08:24,,Information,84,The mail queue was started by login "SQL_MEDICAL\stakes".,,,,10/12/2010 1:08:24 PM,SQL_SERVER\stakes
10/12/2010 13:08:20,,Information,83,The mail queue stopped by login "SQL_MEDICAL\stakes".,,,,10/12/2010 1:08:20 PM,SQL_SERVER\stakes
06/15/2010 02:36:08,,Information,82,DatabaseMail process is shutting down,5080,,,6/15/2010 2:36:08 AM,NT AUTHORITY\SYSTEM
06/15/2010 02:26:08,,Information,81,DatabaseMail process is started,5080,,,6/15/2010 2:26:08 AM,NT AUTHORITY\SYSTEM
It seems that the last time the DatabaseMail process was started, was on 15/06 and never since. The other entries come from my troubleshoot commands.
October 13, 2010 at 6:17 am
that seems to look ok... I have two follow-up questions;
1. How did you verify that access to the mail relay was good?
2. Does your SQL Server sit on a server that has Windows Firewall turned on?
you could try this to verify mail works without DB mail.... (if you wanted)
see attachment....
October 13, 2010 at 6:26 am
I tried it to send mail from the same machine (telnet localhost 25). That works. Besides, there is one weird thing I have to telll. Until I removed it, there was a job (daily backup) which could send an email after finishing...
I also tried to send mail with the firewall off. No result.
October 13, 2010 at 6:32 am
stakes (10/13/2010)
I tried it to send mail from the same machine (telnet localhost 25). That works. .
:hehe: you run mail relay services on your SQL Server????? :hehe:
October 13, 2010 at 6:44 am
Yep, shouldn't I?
October 13, 2010 at 6:50 am
Well, imho, i wouldn't.
Since SQL 2005 and beyond has its own process for sending SMTP mail, I am not sure why you would NEED to relay via your SQL server?
I would try to set up your account and profile to point directly at your exchange server, or whatever email server you use....
October 13, 2010 at 7:02 am
Well, my account does point directly to the email server. I just followed Microsofts instructions on how to configure Database mail (it says first you have to add the SMTP Server feature and configure it).
But that couldn't be the problem. Any other ideas to get my Database mail to work?
October 13, 2010 at 7:08 am
I have to respectfully disagree with you.
you have told me that you know your SQL server can relay properly because you can telnet on port 25 to your local server.
now you say that your account is pointing to your REAL mail server....
what happens when you telnet on port 25 to your REAL mail server?
what happens when you use the script i sent?
It really sounds like your email server is not allowing relay from your SQL Server.
October 14, 2010 at 2:26 am
No problem. I now understand that in the past I have set up relay mail, apparently to make SMTP work. I obviously did not have to do that... (please take in account that I just started with SQL server/Win2008 beginning of this year).
Now we know I don't use the local mailserver, but a remote one, we can go further. To answer your questions:
When I telnet on port 25 on the REAL mailserver, I'm logged in. No blockings or whatsoever.
It took a little time to understand what your script does, but I managed to create the stored procedure (with the REAL mailserver) and send a testmail with it. So the emailserver is allowing relay from my sql server.
December 3, 2010 at 8:14 am
Solved! 🙂
The problem was that an user with only the db_creator serverrole was somehow owner of the msdb database. I changed it to sa and now it works fine.
Thanks for the responses. I'm a SQL Server mail expert now 🙂
BTW: the error message in the Windows Event Viewer triggered me to check the ownership of the msdb
February 3, 2011 at 2:20 pm
In the object explorer, go to ...
Management>>
>>Database Email (right click)
>>>>Click on "Configure Database Mail".
>>>>>>Select "View or change system parameters"
>>>>>>>>The "Database Mail Executable Lifetime (seconds)" is set at 600 seconds by default.
(600 seconds / 60 second in a minute = 10 minutes)
If you look at the Database Mail logs, you can see the service shuts down after ten minutes. I set this value at 43200 seconds which is equivalent to 24 hours.
After changing this, I have never had another problem.
To start the database mail after you have changed the settings, just send a test email by right-clicking on Database Email in the Management tab. When the test email is sent, it will auto-start the service. Keep on eye on the log for after ten minutes to ensure the service does not shut down again.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply