SQL Mail failure

Question

SQL Mail failure

aruram

SSCrazy

Points: 2379
More actions
May 24, 2005 at 9:23 am

#102548

Is there a way to be alerted or notified, if a SQL mail or SQL Agent mail Failure occurs ( For Eg. If the mail session has not been started or a mapi error occurs)
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

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

You can determine if it is running by trying to start SQLMail from a job and noting the return message. This is the best way to check on this and notify someone if it is not running. SQLAgent is a service, so you can use service level alerts in Windows to run a command to send a msg if it fails.

aruram SSCrazy Points: 2379 More actions · Answer 2

Thanks steve, but here are the specific issues I am having :

1) A couple of my scheduled jobs and DTS packages are using sql mail. These jobs run during the night and on weekends. As we have a cluster environment, SQL Mail functionality does not always work properly, for eg. when there are cluster failovers or some other maintenance tasks on the exchange server etc.. If there are any Sql mail failures, I would like to be notified automatically that it has failed via an alert. How can i do this ? Also is there a way to stablize SQL Mail operations in a cluster environment ?

2) I have a couple of alerts setup, but I have noticed that sometimes, I am not notified, and when I test it on an operator, I get an error that the SQL Agent Mail session has not been started. Starting and stopping the SQL Agent Service fixes this problem, but is there a way to know when the SQL Agent Mail session has been stopped. Also, when or why would this happen ?

Thanks

Steven Taylor-205454 SSC Enthusiast Points: 118 More actions · Answer 3

I had exactly the same problem.

The SQLServerAgent service was attempting to start up while the Exchange server was being backed up. Only way I could find round it was to schedule a job to restart the service just before the working day started.

If there's anyway of having an alert for such failures I'd be equally interested.

Steve

Sopheap Suy SSCertifiable Points: 7893 More actions · Answer 4

It can be done but it take a bit of time:

Assume you want to check to see if server A sql-mail is working.

set schedule task on Server B:

step 1: send isql command to send email from Server A to server B.

if step1 failed: go to step 2

if step1 succeed:quit with success

step 2: stop mail on server A using isql command from server B

if step 2 failed: go to step 5

if step 2 succeed: go to step 3

step 3: start mail on server A using isql command from server B

if step 3 failed: go to step 5

if step 3 succeed: go to step 4

step 4: send yourself email from server B to let you know that step 2 and 3 was executed.

if step 4 failed: quit with failure

if step 4 succeed: go to step 1

step 5: send yourself email or paged that sql-mail failed on server A from server B.

there is also another job on server B:

step 1: process email looking for specific subject name and delete it from in-box. If there is no email to process, it send a mail or page from server B to let you know that it didn't receive email from server A thus it assume that sql-mail is down on server A.

Good luck.

good luck