Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 776
  • Last Modified:

MS SQL sending the same e-mail over and over

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
ximbuex
Asked:
ximbuex
  • 9
  • 9
  • 4
  • +1
5 Solutions
 
arnoldCommented:
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
 
ximbuexAuthor Commented:
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
 
Cedric_DCommented:
Emails could be generated by SQL Server Jobs - check them.

0
Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

 
arnoldCommented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
ximbuexAuthor Commented:
Cedric_D: How can I view the SQL jobs without the Object Explorer?  
0
 
Cedric_DCommented:
use msdb
select * from sysjobs

0
 
Cedric_DCommented:
select * from sysjobsteps
0
 
ximbuexAuthor Commented:
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
 
ximbuexAuthor Commented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
ximbuexAuthor Commented:
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
 
ximbuexAuthor Commented:
How can I delete these jobs?  Or at least put them on hold.
0
 
Mark WillsTopic AdvisorCommented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
ximbuexAuthor Commented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
ximbuexAuthor Commented:
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
 
arnoldCommented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
ximbuexAuthor Commented:
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
 
arnoldCommented:
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
 
Mark WillsTopic AdvisorCommented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 9
  • 9
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now