Solved

Why does SQL 2008 send duplicate Email Notifications

Posted on 2013-01-22
39
1,525 Views
Last Modified: 2013-03-29
When I run this query it shows that sql is sending duplicate emails even up to 5 emails for same maintenance job that runs everyday.

select top 100 * from msdb..sysmail_sentitems s order by s.sent_date desc

Rerty option is set to zero

Any ideas why sql 2008 is sending duplicate emails for my jobs?

SQL 2008 Server  64 bit on Windows 2008 64 bit.
0
Comment
Question by:Thomas Grassi
  • 13
  • 11
  • 6
  • +4
39 Comments
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 38808226
I have jobs set up and sending emails on completion in SQL 2008 and they do not get duplicated, there must be some other factors at work.
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 38808235
Yes thats why I posted the question
This all worked fine on SQL 2005 but someting is not correct on SQL 2008

I am running SP1 on SQL 2008
0
 
LVL 24

Expert Comment

by:chaau
ID: 38808434
I think you have multiple profiles configured by accident on your new server. Can you run and check the following queries:

--Profiles
SELECT * FROM msdb.dbo.sysmail_profile

--Accounts
SELECT * FROM msdb.dbo.sysmail_account

--Profile Accounts
select * from msdb.dbo.sysmail_profileaccount

--Principal Profile
select * from msdb.dbo.sysmail_principalprofile

--Mail Server
SELECT * FROM msdb.dbo.sysmail_server
SELECT * FROM msdb.dbo.sysmail_servertype
SELECT * FROM msdb.dbo.sysmail_configuration

--Email Sent Status
SELECT * FROM msdb.dbo.sysmail_allitems
SELECT * FROM msdb.dbo.sysmail_sentitems
SELECT * FROM msdb.dbo.sysmail_unsentitems
SELECT * FROM msdb.dbo.sysmail_faileditems

--Email Status
SELECT SUBSTRING(fail.subject,1,25) AS 'Subject',
       fail.mailitem_id,
       LOG.description
FROM msdb.dbo.sysmail_event_log LOG
join msdb.dbo.sysmail_faileditems fail
ON fail.mailitem_id = LOG.mailitem_id
WHERE event_type = 'error'

--Mail Queues
EXEC msdb.dbo.sysmail_help_queue_sp

--DB Mail Status
EXEC msdb.dbo.sysmail_help_status_sp

Open in new window

0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 38810571
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 38814410
Chaau

Did you get a chance to review my output?

Thanks

tom
0
 
LVL 24

Expert Comment

by:chaau
ID: 38816206
Yes, thank you for the files. I have checked them, indeed I have replicate exactly the same configuration on my server. I could not replicate the problem.

However, what I have learned from your files is that the duplicate emails are sent 5 seconds apart. What this probably could mean that there is a problem with the SQL agent, or the jobs themselves.

You see, the maintenance plans are controlled by the agent, in fact, they are executed by the agent. So, there is either a problem with how the operators setup (please check msdb.dbo.sysoperators table for any suspicious entries), or the actuals maintenance plans.

I found that for the plans you can setup email notifications in two places: in the plan itself, or in the agent (each of your plans will have at one entry there depending on the number of sub-plans you have selected for the plan). In the agent you can also setup the notifications.

You have noticed that you had an old server. How did you create the plans? Have you migrated them somehow from the old server? If yes, try to disabling the existing plans and create a brand new plan from scratch. Make sure that you select the email notification once only in the plan itself. See if it makes the difference. To be 100% safe, please create a brand new operator and set it as a notification recipient.
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 38816932
Chaau

All the plans where created new on he 2008 server.

No migration at all.

When I ran
select top 100 * from msdb..sysmail_sentitems s order by s.sent_date desc

The duplicates were the exact same time

When I created the plan I used the maintenance plan wizard
Choose the notifications see attached

Then in sql agent the jobs I set this

see attached files

I have had this issue for several months and I deleted the plans and recreated them several times no luck
sql.jpg
sql-agent.jpg
0
 
LVL 76

Expert Comment

by:arnold
ID: 38842249
It is impossible to say.
What mail server is being used? Do you have a local smtp (IIS SMTP) service that is being used to send messages out?

Look at the duplicate message headers see whether their message-id is identical and then look at the Received: lines for the timing.
With a local SMTP enabled log you can see whether there are multiple submissions from mssql versus transient errors causing for message retransmital.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38842250
open  your operator -" sqlman" and check what email addresses there : it may send via mail lists 2s
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38842435
This can happen when you have multiple sub plans as part of the job and if one subplan fails, it triggers a chain reaction of failures and you end up getting lots of notifications - seemingly duplicates.

If you look at your report and logging, it says it will apply to all subplans.

So, try notifying just on the subplan.
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 38843426
Arnold

My email server is Exchange 2007 There is nothing wrong with the exchange server or smtp or IIS. I ran this  query select top 100 * from msdb..sysmail_sentitems s order by s.sent_date desc
The results show that SQL is sending the email out. I can post those results if you want to take a look.

EugeneZ
The sqlman operator has just a individual receipient listed not using an email list

mark_wills
All my jobs run to successful completion. No failures.
I do have only the notifications in the sub plan. see above attachments.
The sqlagent has notifications also but only if the job fails.

Thanks all for responding.

I posted this question on Microsoft site and have several others reporting the same issue.

But no one has been able to resolve this.

I have been running SQL 2008 for over 6 months and have had the issue since the beginning.

I was running SQL 2005 and that worked great.

I did not migrate anything from sql 2005. SQL 2008 was a new install and all maint plans were created new from scratch.

Hope we can figure this out.
0
 
LVL 76

Expert Comment

by:arnold
ID: 38843458
Check the logs to see if there are transient errors that lead to the same email being resubmitted.
Have seen in this type of issue many times with different servers. I.e. a sending server connects to the receiving server. They perform their smtp session, the sending server transmits the data. While waiting for the response that the message was accepted from the receiving server, the timeout setting on the sending server is reached. Once the timeout is reached, the sending server closes the connection and notes that the transmission of the message did not complete successfully. The transmission of the message will be retried since this type of error is seen as transient/temporary 4xx.
On the receiving side, the message could well have been accepted but the response it was providing coincided with the connection closure by the sending side.  The receiving server delivers the message to the user. the issue leading to this could be multifaceted.
The sending server timeout is too low. The receiving server has anti-virus/anti-spam on the smtp transaction level that might get more load at some times.

Looking at the smtp logs tracing mailings from one to the other will provide a clearer picture.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38843489
there is no magic -- just way how you setup :

try to create "test" job with notification  (just select 1 and send email on completion):
 did you get 1 or >1 emails?
if just 1 to review or try to recreate your maint (btw -- provide info about your maint plan\job setup)
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 38843495
Arnold,

My network is gigibit the servers are on the same sub net.

Do not have any timeout issues.

smtp logs are clean.

This is a SQL server issue not exchange network or anything like that.

The same network was in place on sql 2005 no problem ever

SQL 2008 problem from the beginning.
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 38843505
EngeneZ

I have created test maint job test maint plans deleted the exisiting plans and recreated them no change.

I use the Maintenance plan wizard and select my jobs and then save. simple as that.

Do this for many years

It is a sql 2008 issue for sure.
0
 
LVL 76

Expert Comment

by:arnold
ID: 38843539
Since you have isolated the issue to the sql. Meaning, the SMTP server log reflects that the sql server transmitted multiple individual messages or a single message with the same recipient included multiple times.

Others pointed at some possibilities. Are these success and failure notifications?
Check the maintenance plan to make sure it does not include a notify operator task that might not have worked in sql 2005 because of a misconfiguration/oversight, but is working now.
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 38843547
Arnold

Yes all jobs are successfull

Forgot to tell you.

No no local SMTP server running
using sql database mail

All my maintenance plans have a notification step defined.

You think I should remove it?

see attached
sql3.jpg
0
 
LVL 76

Expert Comment

by:arnold
ID: 38843586
What are the notifications that are received in multiples? Are you receiving failure notifications only?
Is adding the SMTP role to the SQL an option?

How detailed are the smtp logs on the system through, server005? Could you increase the detail to include the SMTP session details.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 76

Expert Comment

by:arnold
ID: 38843606
The issue could be that your per job failure notification and the global reporting setting are duplicating notifications under certain circumstances.
I.e task1 notify in case of failure.
Within the task, you have a notification requirement.
Your reporting and logging is also configured to generate an email.
I believe it was pointed out that each subplan will generate an email based on reporting and logging settings.
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 38843611
Arnold

I am receiving all success emails
No failures

Why would I need SMTP running on my sql server?
I use database mail using the FQDN pointing to my exchange server using port 25
Thats works great

Would have to look at the smtp log settings

I truely do not think it has anything to do with smtp running on my exchange server
SQL 2008 is creating the email and duplicating it over and over

When I run this query
select top 100 * from msdb..sysmail_sentitems s order by s.sent_date desc

It shows the duplicate emails same message date and time this is on the sql server
0
 
LVL 76

Expert Comment

by:arnold
ID: 38843669
The benefit of having a local smtp is that you could reduce the overhead of an SMTP session.
As well as simplify diagnosig an issue such as you have. I.e. trying to go through the log from exchange. It also eliminates network related or congestion on the mail server.
If your dbmail configuration includes extended logging?
http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

The local SMTP role can be configured to direct all messages through the exchange server (smart host) configuration.
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 38843720
My db mail has extended logging on

reviewed db mail log no errors just normal messages.

I would prefer not to install another role at this time. everything is working fine.

This is a production environment and cannot make changes like that.

Just getting duplicate emails from SQL is what I am trying to get fixed.
0
 
LVL 76

Expert Comment

by:arnold
ID: 38844482
Within the log. After all the jobs ran, how many emails were generated versus the number of email messages received?
Does this system have access to a mail server that is not the exchange server, i.e. a web server that has the SMTP feature/role installed? Recheck your plans and the notification configuration to make sure you are not duplicating the notification requirements.
Determine which jobs you want to be notified about. Make sure those notifications are enabled. Make sure the notifications in the ones you do not want are disabled.
Then uncheck the reporting and logging's email notifications option and see whether the duplicative emails end while the ones you want continue.
Presumably you would want notification on all failed jobs, so make sure those are set.
Within each plan/subplan make sure you do not have a notify operator task.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38845180
what is your sql server edition\sp? you may need to install fresh sq1l service pack

btw:  are you getting email from mAINT PLAN "REPORTING"  and sql job completion?
can you uncheck reporting?

also did you set :test' no maint plan based sql agent job with job complete notification?

 did you get just 1 email? right?
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 38851032
Arnold

The number of emails received matchs the number of emails from the query.

No we do not have access to an external email server.
The Reporting function is on to send an email from the maint plan
In the individual job the notification email is set to send only if failed.
So that is not causes the duplicates.

If I turn off reporting function I will receive no emails

I will test to prove this tonight
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 38851052
EugeneZ

My SQL version is 10.50.2550
which is Service pack 1 plus.

Is service pack 2 out? What is the latest service pack for SQL 2008?

The only time I get an email from sql job is if the job fails.

All the emails are coming from the reporting function.
I know this because I get the details of the job in the email which is an option I checked in the setup. You can see the attached file sql3.jpg  in a prior posting above.

You wrote this????

also did you set :test' no maint plan based sql agent job with job complete notification?

 did you get just 1 email? right?


Do not understand your question here please clairify  Thanks
0
 
LVL 76

Accepted Solution

by:
arnold earned 500 total points
ID: 38851065
The reporting function applies to each subplan.
Are you getting an email.

What it is you are looking to achieve?
Could you provide an example of the duplication you are seeing and are trying to solve.  Disabling the reporting, do the emails continue to flow?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38851264
Well, I suggested a long time ago ( 38842435 ) that the way you had it set up was at the reporting level and that does apply to each subplan,

so, not surprised that a) you only get an email if job fails, b) the number of emails matches the query,

But you also said you were also only receiving "success" emails in 38843611

Can you please describe all the various locations where you have set up any DBmail for alerts or notifications or jobs steps "on failure" type activity.
0
 
LVL 23

Author Closing Comment

by:Thomas Grassi
ID: 38854717
I removed the notifications that was defined in each maintenance plan.
For each job step I changed the email from on failed to on completion.
Now I only get one email for each job step of the maint plan.

Still does not resolve why Notifications of the the maint plan produce multiple emails.
0
 
LVL 76

Expert Comment

by:arnold
ID: 38855045
The report and logging feature functions differently which I believe was pointed out several times before.
One does not execute maintenance pans, the maintenance plans are a way to manage multiple jobs.
 
The further problem you never posted a sample of an email that was generated that you view as duplicate.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38857436
<Still does not resolve why Notifications of the the maint plan produce multiple emails. >
it is how you set your main plans
0
 
LVL 76

Expert Comment

by:arnold
ID: 38857489
A plan does not run, it is a centralized scheduling mechanism.  Notifications are generated by the subplans/jobs
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38857548
if you'll have 1 plan - it will be 1 plan without subplans
but if you put all mains in 1 plan - it will be subplan  - even it is still main plan for specific task
0
 
LVL 76

Expert Comment

by:arnold
ID: 38857693
I believe a maintanance plan starts with a single subplan by default.
The job name would be the maintenanceplan.subplan
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38857945
I agree - it looks like trgrassijr55 setup  side effect:
it means Arnold solution is good one A grade
and this Q took time to get clarifications from trgrassijr55.


Arnold:
it is not so important for this question:

but as idea- "subplan"  vs plan
it is still maint plan and as you know it is much more complicated...with tasks \subtasks\plans\ subplans

again - it is not important- the  main idea for  trgrassijr55
your case it is what you set ..  it does not look like Sql "bug" believe or not


http://msdn.microsoft.com/en-us/library/ms187658(v=sql.105).aspx

Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, is regularly backed up, and is free of inconsistencies. The Maintenance Plan Wizard also creates core maintenance plans, but creating plans manually gives you much more flexibility. Maintenance plans create an Integration Services package, which is run by a SQL Server Agent job. These maintenance tasks can be run manually or automatically at scheduled intervals.
0
 

Expert Comment

by:CrystalReports
ID: 39031879
I also have the same problem that trgrassijr55 has. I have new SQL 2008 R2 64-bit servers running on Windows Server 2008. I have maintenance plans with subplans. The email reporting is configured at the plan level. No email notifications are explicitly configured at the subplan level. When the subplan jobs run to completion they will send an email regadless of whether success, failure, or a warning is reported. Each subplan will send a random number of duplicate emails between zero and 4, for a maximum total of 5 emails. Each duplicate message at the Exchange Server has a unique Message ID, although it is the same job run a the same time with the same results. Again, the quantity of duplicates is random, not systematic.

I can understand trgrassijr55's frustration because he/she has explained him/herself cleary enough. There is a bug in SQL 2008 where a limited number of duplicate emails can be sent by a job. It is not a network, or exachange server, or DBA mail, or SQL mail profile misconfiguration. No one at Microsoft can explain it or has yet heard of it. It happens to a small number of user sites.

 I too would like a solution to this problem.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

707 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

16 Experts available now in Live!

Get 1:1 Help Now