MS SQL sending the same e-mail over and over

You need to check the Exchange SMTP logs on the Exchange server to see whether the email exchange between sql server and the mail.company.com indicates that there is a temporary failure.  You might be better off setting up an SMTP service on the SQL server to avoid a situation where there is an unexpected temporary error eventhough the email was accepted.  This issue usually suggests that the connecting client (sql server in this case) did not wait long enough for the response thast the message was accepted.

Post how and what  leads to the email being generated.
See if you can add debugging options to the email generating mechanism to log what it does and what it gets in response.

I'de prefer to not change anything on the servers (adding an SMTP connection).  Is there a way to find the e-mail in the database and simply remove it so that it does not send any more?  Or can we extend the time that the SMTP service waits for the response from the receiving server?

2008-10-21 23:51:00 192.168.16.14 SRVSQL12K3 SMTPSVC1 192.168.16.12 EHLO
2008-10-21 23:51:00 192.168.16.14 SRVSQL12K3 SMTPSVC1 192.168.16.12 MAIL
2008-10-21 23:51:00 192.168.16.14 SRVSQL12K3 SMTPSVC1 192.168.16.12 RCPT
2008-10-21 23:51:00 192.168.16.14 SRVSQL12K3 SMTPSVC1 192.168.16.12 RCPT
2008-10-21 23:51:00 192.168.16.14 SRVSQL12K3 SMTPSVC1 192.168.16.12 RCPT
2008-10-21 23:51:00 192.168.16.14 SRVSQL12K3 SMTPSVC1 192.168.16.12 QUIT

Select allOpen in new window

Te log is missing the data portion and missing the responses from the server.
The issue might be related whether the SQL mechanism is waiting for the responses from the SMTP server before proceeding or whether it simply dumps the data stream and letting the exchange server process it, but does not wait long enough to get the response from the quit command erroring out.

It is extremely difficult to determine the cause of the issue by looking at the end of the process instead of  the beginning.

Best approach is to first look at the mechanism that generates the email.
If you look at all the messages, you will likely see that each message has a unique Message-ID which would suggest that the process generating the email is continually reconnecting and transmitting similar content rather than a single message being retransmitted.

The addition of the SMTP service to the IIS that is no doubt installed with SQL 2005 is an easy and a recommended mechanism that might solve this issue.

You might be able to avoid the need for an SMTP session by merely creating the email message in the QUEUE/PICKUP mailroot directory on the local system.

What version of SQL - is it actually 2008 ?

Try to find where emails are being generated from - there must be some procedures / notification services being included in your server environment... Are you using sp_send_dbmail  ?

Here are a few items to help look at what is happening :

use msdb
 
EXECUTE dbo.sysmail_help_configure_sp ;
 
EXECUTE dbo.sysmail_stop_sp;
 
select * from sysmail_allitems
 
select * from sysmail_faileditems
 
select * from sysmail_sentitems
 
select * from sysmail_unsentitems
 
EXECUTE dbo.sysmail_start_sp;
 
-- check the is_broker_enabled column of the sys.databases ie alter database <dbname> SET DISABLE_BROKER ;   message will still queue
 
select * from <dbname>.sys.all_sql_modules  where definition like '%mail%'

Select allOpen in new window

Line 17 is missing a few words...

-- check the is_broker_enabled column of the sys.databases ie alter database <dbname> SET DISABLE_BROKER ;   message will still queue

Should have said:

-- check the is_broker_enabled column of the sys.databases and if needed, isolate by turning off the broker db by db ie alter database <dbname> SET DISABLE_BROKER ;   message will still queue

Cedric_D: How can I view the SQL jobs without the Object Explorer?  

use msdb
select * from sysjobs

select * from sysjobsteps

mark_willis: I'm running MS SQL 2005.  I tried to use the query you provided but it returned the following

Server: Msg 2812, Level 16, State 62, Line 3
Could not find stored procedure 'dbo.sysmail_help_configure_sp'.
Server: Msg 2812, Level 16, State 62, Line 5
Could not find stored procedure 'dbo.sysmail_stop_sp'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sysmail_allitems'.

Select allOpen in new window

I just realized that we're running SQL 2000 - I was told it was upgraded at some point.  Our old DBA left the job so I'm stuck trying to figure out how to do SQL stuff.

Mark_willis: That one worked.  The first line was the only one e-mail related:

SEND_MAIL_FOR_EVENTS

ID: 583840967
Text: CREATE  PROCEDURE SEND_MAIL_FOR_EVENTS   AS   SET NOCOUNT ON   DECLARE @OBJECT INT   DECLARE @HRESULT INT   DECLARE @ERRORSOURCE VARCHAR(255)   DECLARE @ERRORDESC VARCHAR(255)   DECLARE @RETVAL INT   DECLARE @CONNRETVAL INT   DECLARE @MAILTO VARCHAR(1000

How can I delete these jobs?  Or at least put them on hold.

What do you mean "the first line"

OK, the procedure is : SEND_MAIL_FOR_EVENTS

so now try :

select o.name as ObjectName,c.text as Definition,* from sysobjects o inner join syscomments c on o.id = c.id where c.text like '%send_mail%'

the above is database by database

Easiest thing to do is to modify the procedure - maybe write to disk instead of send the email for now.

There could be other things - SMTP mail services are not neccessarily in the MSDB database - could also be in any of your databases on that server... Need to check them all.

By first line I meant the first line returned by the query (I'm performing these queries in the SQL Query Analyzer) Here is what the last query returned:

GET_MAIL_ID_FOR_EVENTGROUP_ECOMMERCE
SEND_MAIL_FOR_EVENTS
UPD_ACTIVITYLOG_FROM_EVENTlOG
UPD_ACTIVITYLOG_FROM_EVENTlOG
UPD_ACTIVITYLOG_FROM_EVENTlOG
UPD_ACTIVITYLOG_FROM_EVENTlOG
UPD_ACTIVITYLOG_FROM_EVENTlOG
UPD_ACTIVITYLOG_FROM_EVENTlOG
DELETE_PROCESS_ACTIVITY_LOG
SEND_MAIL_FOR_SHOP
ACRM_MIGRATE_TEMPLATES

Select allOpen in new window

Well now, there are a few others lurking - the send mail for shop... How many databases on that server "SRVSQL12K3"  - might be other things... Need to check the stored procedures SEND_MAIL_FOR_EVENTS and SEND_MAIL_FOR_SHOP to see what it is doing. Also check UPD_ACTIVITYLOG_FROM_EVENTLOG to see when it is calling the procedure...

And probably had better check to see if the MAPI mail client is being used - so - may as well search for xp_sendmail as well... If it is that one, (the 2000 version of sp_send_dbmail, but mapi based not smtp), then it is "central" and can issue an sp_stopmail, but you will need to know a bit more about it when starting up / managing becuase it is linked to a MAPI profile on that machine (ie a Mail client like outlook or exchange server on the machine).

There are 8 databases on the server, four primary ones.  Running select * from sysjobsteps and select * from sysjobs shows nothing with the send_mail_for_events in there.  

Sorry to be a pain..I've never done much of anything in MS SQL!  Thanks for helping me out - hopefully I can get this resolved.  The customer keeps calling me saying they are still getting the email every 3 minutes for almost three days now.

Not being a Pain - more so suffering some Pain. Beside that is what we are here for anyway - to help fix your pain...

arnold does have a very good point though... stop exchange sending stuff.

I have tracked it down to a job that is sending the email.  There is a job running that sends out pending user requestes (such as a password change) every 3 minutes.  

CRM_PROCESS_ACTION.  Inside that job are 6 steps, the last one being "EXEC EXE_ACTIVITY_ACTION". EXE_ACTIVITY_ACTION is a stored procedure that has a dependency called EXECUTE_PROCESS_ACTIVITY, that uses XPE_SEND_EMAIL to send out the email.  

So I am down to this line in the stored procedure: EXEC Master.dbo.XPE_SEND_EMAIL @dbName, @sServerName, @PROCESS_IDS. How can I find what that process is sending out?