Solved

MS SQL sending the same e-mail over and over

Posted on 2008-10-22
26
749 Views
Last Modified: 2009-10-09
Our SQL Server is stuck sending the same e-mail every 3 minutes.  An application on our application server sends customer password requests via e-mail.  The customer's address is valid.  We attempted to turn off the application server (leaving only SQL and Exchange) and the e-mails continued to come.  When turning off the SQL Server the e-mails stop.  

Below is the e-mail message that is being sent out.

Microsoft Mail Internet Headers Version 2.0

Received: from SRVSQL12K3 ([192.168.16.14]) by mail.company.com with Microsoft SMTPSVC(6.0.3790.3959);

	 Tue, 21 Oct 2008 18:21:01 -0400

From: "Company.com" <Info@company.com>

Subject: Company.com Change in login details

To: user@customer.com

Content-Type: multipart/alternative; boundary="=_dY7ASdWQYZa36Aycz55BpKyLqQDPWiYG"

MIME-Version: 1.0

Date: Tue, 21 Oct 2008 18:21:01 +0000

Return-Path: Info@company.com

Message-ID: <SRVMSX2K39UMBvnKqno0000003a@mail.company.com>

X-OriginalArrivalTime: 21 Oct 2008 22:21:01.0374 (UTC) FILETIME=[4CA095E0:01C933CB]

Open in new window

0
Comment
Question by:ximbuex
  • 9
  • 9
  • 4
  • +1
26 Comments
 
LVL 76

Expert Comment

by:arnold
Comment Utility
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.
0
 

Author Comment

by:ximbuex
Comment Utility
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

Open in new window

0
 
LVL 7

Accepted Solution

by:
Cedric_D earned 100 total points
Comment Utility
Emails could be generated by SQL Server Jobs - check them.

0
 
LVL 76

Expert Comment

by:arnold
Comment Utility
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.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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%'

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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

0
 

Author Comment

by:ximbuex
Comment Utility
Cedric_D: How can I view the SQL jobs without the Object Explorer?  
0
 
LVL 7

Expert Comment

by:Cedric_D
Comment Utility
use msdb
select * from sysjobs

0
 
LVL 7

Expert Comment

by:Cedric_D
Comment Utility
select * from sysjobsteps
0
 

Author Comment

by:ximbuex
Comment Utility
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'.

Open in new window

0
 

Author Comment

by:ximbuex
Comment Utility
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.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 200 total points
Comment Utility
In wich case you are probably using some variant of :

EXEC sp_OACreate 'CDO.Message', @MailID OUT
EXEC sp_OACreate 'SMTPsvc.NewMail'

considering there is every reason to believe it is send smtp

check sysjobs and sysjobsteps - those names haven't changed, then:

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 '%sp_OACreate%'
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:ximbuex
Comment Utility
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


0
 

Author Comment

by:ximbuex
Comment Utility
How can I delete these jobs?  Or at least put them on hold.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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.
0
 

Author Comment

by:ximbuex
Comment Utility
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

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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...
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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).
0
 

Author Comment

by:ximbuex
Comment Utility
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.
0
 
LVL 76

Assisted Solution

by:arnold
arnold earned 200 total points
Comment Utility
While you are trying to solve this issue, try adding the Ip of the SQL server to the deny list on the exchange server for relaying and or accepting emails.  This will address one part initially (no more emails to the customer).
When you resolve the cause of the mailing, you would need to clear the queued up emails prior to removing the deny rule on the exchange server.
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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.

0
 

Author Comment

by:ximbuex
Comment Utility
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?
0
 
LVL 76

Assisted Solution

by:arnold
arnold earned 200 total points
Comment Utility
Well if the job runs every 3 minutes, and the event to send an email is triggered, this means that there is a pending user request that has not been addressed.
You need to determine why the mechanism that should send responses to user requests has not done so, or did not 'mark' that the request has been addressed by either deleting the pending request or marking that the task was completed.

You determined what generates the email notification that a request  is still present, you need to track down what function is supposed to address the pending request and generate the notice to the 'requesting user'.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 200 total points
Comment Utility
Yep what arnold said - it is a matter of now back tracking.

But first, comment out that line, fire it up again (but keep blocking e-mails at the server) and make sure that is the culprit - if not done so already. Have you looked inside XPE_send_email ? it might be trying to update an entry or something and simply keeps resending old news...
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now