Suggestions on how to verify if xp_sendmail is working?

Question

Suggestions on how to verify if xp_sendmail is working?

DBADave

SSChampion

Points: 10962
More actions
July 11, 2006 at 8:52 am

#61419

What's a good way to validate if email is working on a daily basis for SQL Server 2000?
I currently receive 40+ emails each day from SQL Server 2000 instances and if I receive less then that I need to determine which servers are experiencing problems. This process is manual and therefor I'm looking for an automated way to verify the status of email communications. Each month we experience email (MAPI) failures on several servers due to DC or Exchange Server reboots or other unknown issues. I don't want to convert everything to SMTP since at some point next year we will start upgrading to SQL Server 2005.
Thanks, Dave

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736458 More actions · Answer 1

You can set a procedure/job to run xp_startmail. If it returns the message it's started, then you know things are ok. If not, I'd suggest you install xp_smtp to send you a note or a "net send" if you have a box available when it runs, to let you know.

DBADave SSChampion Points: 10962 More actions · Answer 2

I'll try that the next time xp_sendmail hangs on a server, but I'm thinking I tried that once before and received a message indicating it is already started. The problem isn't MAPI stopping, but rather hanging. In most cases restarting SQL Server Agent service does not resolve the problem. I need to restart the SQL Server service. Strange thing is we only experience the problem with SQL 2000 Standard Edition and not Enterprise Edition. At my previous employer we had nothing but Enterprise Edition and never had an issue.

Thanks, Dave

DBADave SSChampion Points: 10962 More actions · Answer 3

Hi Steve,

Unfortunately your suggestion didn't work. We just rebooted our domain controllers and sure enough several of our SQL Servers experienced the MAPI email problem where xp_sendmail hangs. I ran xp_startmail and received the message indicating "SQL Mail session is already started" and as usual, when I stop the query the process is not killed. When I try to kill the process it gets stuck in a Rollback state. Restarting SQL Server Agent releases the process, however the next time I run xp_sendmail it hangs. The only solution is to restart the SQL Server service.

Any other suggestions on how to monitor SQL Mail?

Thanks, Dave

James Reid-320013 Valued Member Points: 56 More actions · Answer 4

You shoudl try doing an xp_stopmail and then an xp_startmail.

Also, if you have agent jobs set up that use send mail based alerts, you should also restart the agent service , its a real pain when one discovers that falied job alerts failed to send.

cheers

Jim

DBADave SSChampion Points: 10962 More actions · Answer 5

I've tried that and unfortunately it didn't solve the problem and neither does restarting the SQL Server Agent service. Part of the problem is explained as follows.

"The SQL Server Agent mail connectivity is handled by a single mail interface thread. All the SQL Server Agent job uses this mail interface thread for is e-mail communication. Each SQL Server Agent job waits without any timeout limit until the mail command of the job returns from the mail interface thread. If the mail interface thread stops responding because of some external reason, the dependent job keeps waiting infinitely."

"What happens is that the mail client attempts to connect to the mail server. If the connection fails, a message from the mail client appears that requests user action as to whether to continue or cancel. The message does not appear on the server because SQL Mail is run from the SQL Server service. Because a message does not appear, no user action can occur and SQL Mail waits indefinitely. Even if you re-establish the network connection, SQL Mail does not automatically reset and retry. You must stop and restart the SQL Server service before you can begin sending messages again.

Another possible reason for SQL Mail to stop responding is a new Outlook security feature that warns when a non-Outlook application is sending e-mail. SQL Mail for SQL Server 6.5 and SQL Server 7.0 are such non-Outlook applications. If a security dialog box window appears when SQL Mail is sent, this has the same effect of making SQL Mail stop responding as if the connection to the mail server were lost."

I decided to create a second job, which runs 10 minutes following the job that tests SQL Mail communications. The second job checks sysjobhistory to see if the first job completed. If it didn't, I raise an error which is written to the application event log. A rule was then created in Microsoft Operations Manager to detect the error and send an email to me, notifying me of the job failure. Another option in place of MOM was to create an Alert that fires when the specific error is detected. The error could send me an email to indicate a problem with MAPI.

Thanks, Dave

Zubeyir SSCrazy Points: 2559 More actions · Answer 6

Hi Dave,

Why do you need to use SQL Mail? We in our shop used to have SQL Mail but most completely get rid of it because of this kind of problems. When our boss asked me to find alternative ways I found a lot. (xp_smtp was a great one!)

Mainly sending the mail instantly when the T-SQL command executes is not a good idea! We prefer to save them to a table a send them by a job that runs seperately. (Which is easy to manage too)

For sending mails that are saved to this table you can have different options but coding an windows service that uses SMTP is the best practise (I know so far )

In that way you can have templates to apply the mails you are going to send. We even have the "@query" option to run queries.

Cheers

Zubeyir

DBADave SSChampion Points: 10962 More actions · Answer 7

Hi Zubeyir,

We have over 50 SQL Servers so converting to SMTP will take some planning. I was hoping to do this later in the year, but have reconsidered since we will begin upgrading to SQL 2005 next year. Once that happens MAPI will no longer be an issue.

Thanks, Dave