October 8, 2008 at 9:38 am
Emails from job are being put in the queue
SELECT * FROM sysmail_allitems
with status of unsent
So if i run following
EXECUTE dbo.sysmail_start_sp
status is set to sent and emails are fired.
Anyone know why it wouldn't automatically start firing. Was working fine now stopping.
Cheers
October 8, 2008 at 9:43 am
Not being a Service Broker expert I can't give you a solution, but the Queue's used by Database Mail are not started so Database Mail doesn't send the emails. The procedure you are running starts those Queues. I don't know why the Queue's are not started nor do I know why they would not start automatically. Check the SQL Server logs and the Windows Event logs.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 8, 2008 at 9:56 am
Are u getting any errors or its says command completed and not processing any mails 🙂
October 12, 2008 at 7:16 am
Nothing in the logs at all...they just sit in the queue as unsent.
October 14, 2008 at 2:16 pm
I see the following DatabaseMail process is shutting down
So if i start it - it fires emails...Then it shutsdown again.
My email uses the
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBA_SystemAdministrator',
@recipients = @Emailrecipients,
@subject = @EmailSubject,
@body = @tableHTML,
@body_format = 'HTML' ;
Then when the databasemail shuts down the email sits in queue and does not get send unless i do manual start of the email .
Never had to force it to start so should i be issuing EXECUTE dbo.sysmail_start_sp before hand.
Just wondering why email is not restarting itself. It shutdown on its on nicely...just not starts
October 14, 2008 at 4:04 pm
TRACEY (10/14/2008)
I see the following DatabaseMail process is shutting downSo if i start it - it fires emails...Then it shutsdown again.
How long does it take before it shuts down again? Is there anything going on the server that corresponds with when it is shutting down?
And please check the SQL Server error logs again, because there really should be something in there right before it shuts down.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 14, 2008 at 5:35 pm
10 minutes then it stops which i believe is normal for the system to turn the mail off it no activity.
But then it does not turn on when you fire email.
Nothing in logs wish there was make my job easy to do.
So if i then manually do execute job it starts the databasemail started ....then same thing 10 minutes later shutdown.
October 15, 2008 at 5:23 am
Hi,
Since you are using DB mail to send mails, Db mail uses SMTP relay server for sending mails. Just check if you have access to SMTP relay mail server & also if you have used the relay mail server name while configuring the DB mail.
Have A nice Day !!!
----------------------------------------------------------------
**"There is only one difference between dream and aim. Dream requires Soundless sleep to see…Where as Aim Requires Sleepless Efforts to Achieve":-) **
October 15, 2008 at 7:55 am
OK, I have done some research on thi sand here is what I have found:
TRACEY (10/14/2008)
10 minutes then it stops which i believe is normal for the system to turn the mail off it no activity.But then it does not turn on when you fire email.
This is normal also. When it shuts down, it disables the Service Broker queues, so when you send mail later on there is no notification event to wake the service back up again. I do not know why they do it this way, as it is pretty easy for the Receiver of a Service Broker queue to manage the timing issues, instead of shutting the queue down.
Anyway, there are two choices available to you:
1) Your sending procs can check to see if DBMail is shut down, and restart it if it is, like so:IF Not Exists(SELECT * From msdb.sys.service_queues
Where name = N'ExternalMailQueue'
And is_receive_enabled = 1)
EXEC msdb.dbo.sysmail_start_sp
or
2) Change the "Database Mail Executable Minimum Liftime" from the default 600 seconds (10 minutes) to whatever you want. The doc does not mention if a setting of zero means "never shutdown" or "shutdown as soon as there is no pending messages" and I cannot test it right now. Perhaps somebody else can?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 15, 2008 at 8:06 am
Thanks i am still on this thread - just read every thread from ms.
Does seem odd that it doesn't start up - because this had been working.
Perhaps i had another job that was every 10 minutes and removed this one can't remember and so the database mail was still around open cause it had been firing emails.
But in my case i reached the limit so i now have to restart it.
I will add that part to my code and see what happens.
I have another server with same job and that just fires on it own - shutsdown and then every hour still fires off on it own - i monitor the log today and see what i get before it fires the email.
In mean time i add the code to procedure.
Thanks for researching i add anything else to this thread if i find anything
October 15, 2008 at 8:14 am
It is curious Tracey. I have been using it myself on sevral servers and I have never noticed it shutting down before. Perhaps my SQL Agent Email Alerts have been waking it up periodically and I just never noticed the delay in receiving the application emails.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 16, 2008 at 10:19 am
If i run the procedure in sql query the databasemail gets started and sends email.
If the job runs the procedure it will not start the databsemail sits in unsent.
The job is run as sa so full rights is here.
Added following and then even just the one line
IF Not Exists(SELECT * From msdb.sys.service_queues
Where name = N'ExternalMailQueue'
And is_receive_enabled = 1)
EXEC msdb.dbo.sysmail_start_sp
EXEC msdb.dbo.sysmail_start_sp
The databasemail will not fire when kicked off by a job only if job ran manually or through query analyzer.
Bizarre...
October 16, 2008 at 10:32 am
Yeah, I would take that to Microsoft, because it sounds like a bug to me.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 16, 2008 at 10:47 am
It might happen if your SMTP virual server authentication is set to Integrated Windows instead of Anonymous.
The "unsent" status is ALWAYS linked to SMTP configuration.
October 16, 2008 at 11:03 am
It might happen if your SMTP virual server authentication is set to Integrated Windows instead of Anonymous.
The "unsent" status is ALWAYS linked to SMTP configuration.
I can't see where the services are for SMTP used to be IIS for SMTP i check with the network people the settings.
If not then time to call the boys in blue.....
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply