October 5, 2009 at 4:25 am
Hi All,
I am using SQL Server 2005 with SP2 (3282). I have configured database mail on the server. Test mail works fine, however SQL agent job is unable to send email upon its completion (success or failure). The job logs shows that, mail is queued but we never receive email from this server.
I have selected the profiler in AgentProperties->Alerts and tried re-starting agent and SQL server. None of them are able to fix the problem.
Any help is highly appreacited.
Thanks,
-Mohsin
October 5, 2009 at 4:32 am
What does it say in the logs, and is there any messages in any of the event logs
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 5, 2009 at 4:46 am
Job logs report as follows,
"Mail queued. [SQLSTATE 01000]
Job 'DBAdmin--- Space alert' : Step 2, 'email' : Began Executing 2009-10-05 02:42:28"
No errors in event viewer. If I am using script to send mail, it works fine in SSMS, however the same script used in the job stpe unable to send email.
October 5, 2009 at 4:51 am
Mohammed Mohsin-392707 (10/5/2009)
Job logs report as follows,"Mail queued. [SQLSTATE 01000]
Job 'DBAdmin--- Space alert' : Step 2, 'email' : Began Executing 2009-10-05 02:42:28"
No errors in event viewer. If I am using script to send mail, it works fine in SSMS, however the same script used in the job stpe unable to send email.
could be down to permissions perhaps and profiles, what account are you using when running it as a script compared to what you are running as the sql agent account.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 5, 2009 at 5:06 am
Job is executed with local admin as "NT AUTHORITY\SYSTEM"
October 5, 2009 at 6:17 am
There is not enough information to help you.
You may want to do some modifications in your job to provide more info.
Click on edit your job step, click the advance tab... and have the sql job out the results to a file.
Let the agent execute the job and check the output file for error msgs.
October 5, 2009 at 6:47 am
I tried all the steps mentioned by you. None of them report any error messages.
Thanks,
-Mohsin
October 6, 2009 at 7:02 am
Check your Virus Protection Log.
October 6, 2009 at 7:07 am
edit - just read your post properly and you have already done what i suggested 🙂
October 6, 2009 at 7:14 am
Try running this script and let us know what you see in the failure description, or if you are not seeing anything at all
use MSDB
go
select EL.Description as FailureReason, FI.* from dbo.sysmail_event_log EL
inner join dbo.sysmail_faileditems FI on FI.mailitem_id = EL.mailitem_id
order by log_id desc
October 7, 2009 at 4:30 am
I am getting no errors upon executing your query.
October 7, 2009 at 4:31 am
I am getting zero rows upon executing your query.
October 7, 2009 at 4:36 am
ok i assume you are not getting anything in the failed events table then. Sounds like your agent job isnt even getting as far as sending a message to the broker. is there anything returned by the dbo.sysmail_unsentitems view.
October 7, 2009 at 4:38 am
if you can run this ok using a script but not as a scheduled job. You have said that the sql agent is running under the 'NT AUTHORITY\SYSTEM' login
run your script using the security context of this account
execute as login = 'NT AUTHORITY\SYSTEM'
-- Your script goes here
see if that works, if it doesnt then you dont have the permissions set right
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 7, 2009 at 4:44 am
I tried running the above script, however there are zero rows returning from the query.
Thanks,
-MMohsin
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply