SQL Agent Mail issue

Last Post 27 Mar 2003 07:44 PM by bigelectricmac. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
bigelectricmac
New Member
New Member

--
23 Mar 2003 03:46 PM
Looking for some assistance if anybody can help ...

I have an issue whereby SQL Agent mail appears to be hanging, but I have nothing to help tell me why in either Error Log or Event Viewer.

I have various overnight jobs that run and send an e-mail to me upon success/failure. In the past these emails have been sent pretty reliably. However for the last week, things have changed. The jobs run in this sequence -

2100 - A database Maintenance Plan runs and emails a text report as part of the Reporting for this plan. The email arrives succesfully with the text report attached.

0600 - Job runs which executes a DTS package, but no email is sent although one is set up in the Notifications section of the job. In View Job History it shows Step 1 as successful, but there is no step 0 (which would be the e-mail notification). In the Jobs window the status is showing as Performing Completion Actions.

0630 and 0700 - Other jobs run with the same outcome as the 6am job.

If I check the SQL Agent mail in EM, it is no longer running and I have to stop and start the SQL Agent service to get it going again. SQL Mail is running and appears to be unaffected.

The only thing that has changed recently is that within my company we have been allocated a sub-net and now the server is acting as the DHCP server also. This has meant that its IP address has changed. I am unsure how this might affect things, as I would have expected ALL emails to fail if this was the cause.

Thanks for any light anybody can shed on this for me!

Kind regards

Paul
satya
New Member
New Member

--
24 Mar 2003 11:43 AM
What is the SP Level on the SQL server and OS?
If possible restart the SQL services, this may not be a solution but sometimes this would resolve any problems.

HTH
mimadon
New Member
New Member

--
24 Mar 2003 12:25 PM
This has been a known issue for both SQLMail and SQLAgent Mail in SQL 2000 for some time now.

I can't find the KnowledgeBase article today, but the short description (from memory) went something like:

When using POP3 or SMTP clients with SQLMail and SQLAgent Mail, when an attempt to use the mail client fails to connect to the mail server, in some instances the attempt to send will hang. Any subsequent attempts to use the mail integration feature will also hang. This problem, although more unlikely, can also happen when using Exchange mail services.

To resolve, stop and restart the affected service - SQL Server in the case of SQLMail or SQLAgent in the case of SQLAgent Mail.

I have no solution (and apparently neither does MS), but I too feel your pain....

bigelectricmac
New Member
New Member

--
24 Mar 2003 12:26 PM
I am on SP3 for both.

As I said, this was working fine before last weekend. It has happened before once or twice, but by restarting the services it always cured the problem. Now it doesn't.

The machine always has a re-boot on a Sunday night, but still the problem persists.

Many thanks

Paul


mimadon
New Member
New Member

--
25 Mar 2003 10:41 AM
Just wanted to add a short comment for clarification:

We also experience this problem occasionally. When the problem occurs, the initial problem connecting to the mail server (Exchange in our case) is usually related to network connectivity to the mail server or the mail server itself, not the machine running SQL Server.

bigelectricmac
New Member
New Member

--
27 Mar 2003 12:37 PM
It appears I have now resolved my problem and I thought I would post the sequence of events, just in case it helps anybody.

I was assuming that my first job was sending an e-mail and then hanging the SQL Agent mail service, but it appears this is not the case.

I noticed that there was no e-mail in the Sent Items within Outlook after the Maintenance Plan job had finished and the e-mail had been sent (In SQL Agent Properties I have enabled the 'Save copies of the sent messages in the "Sent Items" folder'). All SQL Agent messages deposit a copy of the message in the Sent Items folder. This made me realise that the e-mail notification from a Maintenance Plan is sent by SQL Mail and not SQL Agent Mail.

The first job that runs is a scheduled Maintenance Plan and as such, the notification e-mail is set up inside the plan and not in the Job. The two Jobs that follow are DTS packages and the e-mail notifications are set up within the Job.

As a result, I wondered whether the first job was actually impacting on SQL Agent Mail at all, so I disabled the second Job and then recreated it afresh. Next time the jobs ran, the e-mail notifications were sent as normal, so it would appear to me that the second job (but the first one to use SQL Agent Mail) was hanging SQL Agent Mail and once I took this out of the loop, things were working again.

I hope this might help anybody else with the same issues.

If anybody knows how to set SQL Mail to save copies of sent messages into the Sent Items folder, I would be grateful if you could let me know!
bigelectricmac
New Member
New Member

--
27 Mar 2003 07:44 PM
If you have setup the job in such a way that to continue the second job irrespective of the first job fails or not, then there should not be any effect. To me this looks like the problem dissapeared because you re-created the maint. plan. Do the jobs seperately and test. Then you can see the cause
You are not authorized to post a reply.

Acceptable Use Policy