Learn how to a build a cloud-first strategyRegister Now

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

Why does SQL 2008 send duplicate Email Notifications

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
Thomas Grassi
Asked:
Thomas Grassi
  • 13
  • 11
  • 6
  • +4
1 Solution
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
chaauCommented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
Thomas GrassiSystems AdministratorAuthor Commented:
0
 
Thomas GrassiSystems AdministratorAuthor Commented:
Chaau

Did you get a chance to review my output?

Thanks

tom
0
 
chaauCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
arnoldCommented:
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
 
Eugene ZCommented:
open  your operator -" sqlman" and check what email addresses there : it may send via mail lists 2s
0
 
Mark WillsTopic AdvisorCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
arnoldCommented:
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
 
Eugene ZCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
arnoldCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
arnoldCommented:
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
 
arnoldCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
arnoldCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
arnoldCommented:
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
 
Eugene ZCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
arnoldCommented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
arnoldCommented:
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
 
Eugene ZCommented:
<Still does not resolve why Notifications of the the maint plan produce multiple emails. >
it is how you set your main plans
0
 
arnoldCommented:
A plan does not run, it is a centralized scheduling mechanism.  Notifications are generated by the subplans/jobs
0
 
Eugene ZCommented:
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
 
arnoldCommented:
I believe a maintanance plan starts with a single subplan by default.
The job name would be the maintenanceplan.subplan
0
 
Eugene ZCommented:
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
 
CrystalReportsCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 13
  • 11
  • 6
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now