January 12, 2005 at 10:30 pm
From time to time xp_sendmail gets stuck in an executing state. Every call to xp_sendmail after that also gets stuck in this state. We are unable to kill off the processes or to stop and start mail. The only way we have been able to fix it is to stop and start SQL Server.
We run Microsoft Exchange 2003 as our Exchange Server and our Outlook clients on our SQL Servers are 2002. We have our SQL Server 2000 boxes at SP3a, hotfix 923.
The problem seems to be pointing more to a response issue with our Exchange Server, but we are unable to trouble shoot this. Any suggestions?
Cheers
January 13, 2005 at 2:35 am
Hi trish,
We've has a similar problem in the past too, the problem being that xp_sendmail is an extended stored procedure and we discovered that once one of these processes goes awry there's nothing you can do to kill it (other than bounce the SQL box). We moved to using xp_sendSMTP instead which has an automatic timeout, which means that if a command doesnt complete within a configurable period of time then it is killed automatically, to the best of my knowledge this is handled internally in the dll so even if communication with it is lost it still dies. Since making this switch we havent had a single occurance of this.
I did investigate the possiblity of identifying the actual thread that it was using in the SQL Server process (with process handler from sysinternals) so that i could kill the individual thread and stop the problem, but in the end i decided that this was a bit too risky on a production box. It worked on a test server but i didn't want to do it on the live one in case it caused other hidden problems.
Hope this helps
January 13, 2005 at 2:29 pm
Interesting discussion. I've got a customer who wants a trigger setup for using xp_sendmail and I'm glad to have this info ahead of time.
Here is a possibly relevent kb article I found on the topic:
http://support.microsoft.com/default.aspx?scid=kb;en-us;164998
January 13, 2005 at 4:28 pm
Hi,
Thanks for the replies. We have also tried killing off the thread. It didn't do any damage to SQL Server, but didn't stop the process either.
We are upgrading to Windows 2003 with the 2003 Outlook client. If this doesn't resolve the problem, we may need to rewrite our application code to use SMTP. Its a last resort to rewrite our code, but may be necessary as the problem has dragged on for too long now and can't come up with any other solution.
January 14, 2005 at 9:44 am
we've also come across this problem at a site we visited recently
we tracked this back to the following
http://support.microsoft.com/default.aspx?scid=kb;en-us;328197
the exhange server became unavailable (for a directory lookup) as sql agent tried to send a mail.
this stalled the mail agent and we couldn't kill the process without stopping and restarting SQL server.
MVDBA
January 14, 2005 at 11:20 am
I usually find the problem is caused by the exchange server being unavailable.
Stopping and restarting the sql server is the only way I have found to fix the problem
Steven
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply