I set up sqlmail in our SQL Server 2000 server in a Windows 2000 machine to send out email reminder. I have a daily schedule job to run a stored procedure that I created and execute xp_sendmail to send out the email reminder.
One day it just stopped working…
I went to enterprised manager to check on the job history and found the job is still in “executing” phases and never ended, it has been running for more than 4 hours! I tried restart sqlserver agent, killed the hung process and the process just didn’t go away(stay at “KILLED\ROLLBACK” status even the job is just a simple xp_sendmail task). I tried xp_stopmail and got the message:
xp_stopmail: Failed to stop SQL Mail session.
Many posts I found online recommended to restart the SQL Server services but I really didn’t want to have downtime at all for my users, so I continue reading and search online…
Fortunately, I came across a post regarding killing the “MAPISP32.EXE” from the Task Manager. Running out of choice so I gave it a try, and right after I killed the “MAPISP32.EXE” process my “KILLED\ROLLBACK” processes went away(finally! after hung there for 8 hours)! I then went to query analyzer and issued
xp_stopmail
xp_startmail
After that I tried to send out a test email using xp_sendmail and everything just work fine!
I came across the problem again this morning and here are the steps I did to make the hung SQLMail go away:
Step 1: login to the Server, opened up Task Manager, find MAPISP32.EXE and ended the process
Step 2: open up Query Analyzer, execute
xp_stopmail
xp_startmail
to restart SQLMailStep 3: Test the SQLMail by execute
exec master.dbo.xp_sendmail @recipients = 'youremail@yourdomain.com',
@subject = 'test sqlmail after restart',
@message = 'test sqlmail after restart - email body'
Good luck and hope this help.