June 22, 2005 at 2:22 am
I have a number of overnight jobs that email me once they are complete.
If I don't get an email in the morning then I know to check the server to find out why.
Every now and again I get a number of jobs that are hung with a status of "Running Completion Tasks". Short of restarting SQLSERVERAGENT I can't find a way of unjamming these tasks.
The main bulk of the jobs work fine, the problem is purely at the notification stage.
Has anyone come across this and if so, how did they resolve it?
June 23, 2005 at 3:36 am
The root cause of the problem is a glitch in the mail processing. There are a few KB articles about different variations of this, and how long the impact lasts after Exchange and/or the SQL Mail Profile are working again.
My experence is that some issues just affect the job(s) that is hung, some require the SQL Agent to be restarted, some require SQL Server to be restarted, and some even need a reboot.
I tend to start by trying to stop the job in EM.
If this does not work I try to kill the thread. In a number of situations SQL Agent has created a second thread that is stuck in SQLDMO processing. If this exists and is also killed, the job disappears. In this situation the job can be rerun and often works OK on the rerun.
Sometimes the kill command (more accurately described as the 'shout very loudly and hope you scare it away' command) leaved the job thread in a killed/rollback state. In this situation SQL Agent needs to be restarted. If the Agent closedown leaves some threads for the failed jobs still existing, you need to restart SQL to clear these.
If the mail has been sent by xp_sendmail, rather than using operator-based notification, then xp_sendmail can get disabled. There is a KB that states if xp_sendmail encounters a timeout on Exchange, then it is disabled until SQL Server is restarted.
Finally, the SQL Mail Profile can get corrupted. The cause seems to be related to the fact the software comes from Microsoft. Sometime this can be resolved by logging on with the SQL service account, then running Outlook. Sometimes you also need to edit the Outlook mail profile and force a re-validation of the mailbox name (I normally delete and re-type the last character of the mailbox, and click on 'Check Name').
Sometimes a mail profile problem needs a Windows reboot to clear. However, we have had a few situations on NT4 where a new mail profile needed to be created, and even had one where we had to rebuild the box to get mail working again - this has not happened sine we moved to W2003, but so far we have far fewer server-years on W2003 than we had on NT4 so there could be some nasties waiting to happen.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 23, 2005 at 3:46 am
Thanks for this.
I've resorted to having a windows CMD file run with
NET STOP SQLSERVERAGENT
NET START SQLSERVERAGENT at 06:00 every morning.
It is crude and horrible I know but 9 points for coming up with a solution that works, 1 point for it looking pretty.
The problem seems to be caused by outages in our Exchange server. We are based in the UK and the Exchange server is based in the US. Apparently timeouts are causing the problem.
June 23, 2005 at 4:26 am
The issues around having Exchange and database servers distributed around the world seem the sort of problem you could get a good consultancy organisation to find a solution to...
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 23, 2005 at 12:00 pm
If you switch to using SMTP Mail or CDOSYS Mail the problem will also go away.
Here's the link if you're interested:
http://support.microsoft.com/default.aspx?scid=kb;en-us;312839
I've shunned SQL Mail since it's inception due to the buggy and disruptive nature of the beast. SQL 2005 will change all this and natively support SMTP mail ending the SQL Mail debacle. Mind you I'm notbashing the SQL Mail utility, it's just my experience has always left me with a bad tase. There are many others who swear by it.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
June 28, 2005 at 12:53 am
What service pack is your sql server?
Had this problem a long time ago on sp2. Upgraded to
sp3a has since gone away.
By the way if your after a great smtp mail app for sql check out
Fantastic and cheap!
June 28, 2005 at 8:30 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply