Sign in
 
Home20122008 R2200820052000LibraryForums
Note: Forums will be making significant UX changes to address key usability improvements surrounding search, discoverability and navigation. To learn more about these changes please visit the announcement which can be found HERE.
SQL Server >
> 
> 
SQL 2008 Database Mail and Notifications

תשובה SQL 2008 Database Mail and Notifications

  • Monday, October 08, 2012 12:25 PM
     
     
    Sign In to Vote
    0

    Windows 2008 R2 Standard 64 bit Server with SQl 2008 Standard 64 Bit

    I setup several Maintenance Plans for all my SQl databases on three instances.

    SQLAgent runs great all the jobs run successfully from the Maintenance Plans.

    After each job that runs I get notified via email. That also is working great.

    The problem is that I am getting up to 4 emails for the exact same job

    I do not understand why I am getting duplicate emails from the Database Mail and notifications.

    If I run the job manually I only get one email notification

    The multiple emails come when the job is scsheduled to run. I checked the logs and the job only runs once also checked the database mail log did not see any thing strange theere.

    ANy ideas why the email notifications are duplicating themselfs? 2 3 even 4 times?

    Thanks

    Tom

    Thomas R Grassi Jr

     

All Replies

  • Monday, October 08, 2012 1:36 PM
    Moderator
     
     Answered
    Sign In to Vote
    1

    Hello,

    Have you set the “Retry attempts:”  option? Maybe that is the reason is sending that many emails. One per fail/retry.

    Locate this option on the steps of the job. Edit a step and go to advanced options.

    Hope this helps.

    Regards,
    Alberto Morillo
    SQLCoffee.com

    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:32 PM
    •  
     
  • Monday, October 08, 2012 1:47 PM
     
     Answered
    Sign In to Vote
    0

    Alberto

    Thanks for reponding was not sure if I asked this correctly.

    I checked the advanced options and no I did not set any retry attempts

    On success action

    quit the job reporting success    retry attempts 0 rerty interval 0

    On failure action

    quit the job reporting failure

    Thats on all my jobs

    This is a strange one most people on here seem to not get emails to work I get it to work but get more than I asked for LOL

    ANy other ideas?

    Tom

    Thomas R Grassi Jr

    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:32 PM
    •  
     
  • Monday, October 08, 2012 2:17 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    1

    Does the job has multiple steps? Can you please check below catalog views.

    Select * from sysmail_event_log
go
Select * from sysmail_allitems 
go
Select * from sysmail_sentitems
go
Select * from sysmail_unsentitems
go

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:33 PM
    •  
     
  • Monday, October 08, 2012 2:19 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hello,

    I found the following related thread:

    http://social.msdn.microsoft.com/Forums/en/sqltools/thread/05ffd056-db05-46be-bd6a-094aa7aadfa6  

    Is the job called by an alert? The default delay for alert responses is every zero minutes.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:34 PM
    •  
     
  • Monday, October 08, 2012 2:46 PM
     
     Answered
    Sign In to Vote
    0

    Job only has one step

    I do have one job that has two steps defined but that only runs on Sunday.

    I tried your query but I got this

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'sysmail_event_log'.

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'sysmail_allitems'.

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'sysmail_sentitems'.

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'sysmail_unsentitems'

    Any ideas?

    Thomas R Grassi Jr

    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:33 PM
    •  
     
  • Monday, October 08, 2012 2:52 PM
     
     Answered
    Sign In to Vote
    0

    Alberto

    I did not define any alerts

    The link was helpfull but not my issue

    The smtp serer is fine It works for many other applications that I have running on my network

    Just SQL 2008 sending many emails for the exact same job.

    The jobs are only one step

    Still puzzeled

    Tom

    Thomas R Grassi Jr

    update

    In the job I only defined a notification

    Selected only email then choose the operator  and When the job fails is selected

    • Edited by TRGOneCare Monday, October 08, 2012 3:52 PM
    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:34 PM
    •  
     
  • Monday, October 08, 2012 2:56 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Job only has one step

    I do have one job that has two steps defined but that only runs on Sunday.

    I tried your query but I got this

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'sysmail_event_log'.

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'sysmail_allitems'.

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'sysmail_sentitems'.

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'sysmail_unsentitems'

    Any ideas?

    Thomas R Grassi Jr


    you need to run that under context of msdb database (Add "Use MSDB" at top). All I am trying to see if SQL is sending multiple reqests to SMTP or something wrong somewhere else.

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:33 PM
    •  
     
  • Monday, October 08, 2012 3:25 PM
     
     Answered
    Sign In to Vote
    0

    That worked here is the report in text format

    The file is too large to paste into this window how can I get it to you?

    Thanks

    Tom

    Thomas R Grassi Jr

    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:33 PM
    •  
     
  • Monday, October 08, 2012 4:02 PM
    Moderator
     
     Answered
    Sign In to Vote
    1

    That worked here is the report in text format

    The file is too large to paste into this window how can I get it to you?

    Thanks

    Tom

    Thomas R Grassi Jr


    You can keep that file on any free file hosting site and provide link to us in next post.

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:33 PM
    •  
     
  • Monday, October 08, 2012 4:05 PM
     
     Answered
    Sign In to Vote
    0

    Placed it here

    https://dl.dropbox.com/u/85371542/sqldbmail.txt

    Thanks

    Thomas R Grassi Jr

    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:33 PM
    •  
     
  • Tuesday, October 09, 2012 12:25 PM
     
     Answered
    Sign In to Vote
    0
    Any luck with my file?

    Thomas R Grassi Jr

    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:33 PM
    •  
     
  • Tuesday, October 09, 2012 12:27 PM
     
     Answered
    Sign In to Vote
    0

    Alberto

    Today still getting more than one email per job

    The above link was helpfull but all the areas he covered are good

    This is a strange one.

    Thomas R Grassi Jr

    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:34 PM
    •  
     
  • Wednesday, October 10, 2012 12:25 PM
    Moderator
     
     Answered
    Sign In to Vote
    1
    Any luck with my file?

    Thomas R Grassi Jr


    Looks like SQL is sending the emaill. Now, next step would be to put profiler trace and gather more data.

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:33 PM
    •  
     
  • Wednesday, October 10, 2012 1:31 PM
     
     
    Sign In to Vote
    0

    Ok how do I do that?

    Thomas R Grassi Jr

    Found it SQL Server Profiler under performance tools

    I run it tonight on one of the instances

    Update

    Sorry have not be able to run traces, Microsoft came out with updates and been busy updating all computers past couple of days.

    Will run trace over the weekend.

    One side note: When I run the job from the SSMS I only receive one (1) email notification.

    So only when the job is scheduled I get multiple emails.

    Will post trace reults as soon as I can.

    • Edited by TRGOneCare Friday, October 12, 2012 6:06 PM
    •  
     
  • Saturday, October 13, 2012 3:00 PM
     
     Answered
    Sign In to Vote
    0

    I ran the SQL Server Profiler trace

    The files are located here:

    https://dl.dropbox.com/u/85371542/EMail.trc

    https://dl.dropbox.com/u/85371542/EMail_1.trc

    https://dl.dropbox.com/u/85371542/EMail_2.trc

    https://dl.dropbox.com/u/85371542/EMail_3.trc

    https://dl.dropbox.com/u/85371542/EMail_4.trc

    https://dl.dropbox.com/u/85371542/EMail_5.trc

    https://dl.dropbox.com/u/85371542/EMail_6.trc

    https://dl.dropbox.com/u/85371542/EMail_7.trc

    https://dl.dropbox.com/u/85371542/EMail_8.trc

    https://dl.dropbox.com/u/85371542/EMail_9.trc

    https://dl.dropbox.com/u/85371542/EMail_10.trc

    https://dl.dropbox.com/u/85371542/EMail_11.trc

    https://dl.dropbox.com/u/85371542/EMail_12.trc

    https://dl.dropbox.com/u/85371542/EMail_13.trc

    https://dl.dropbox.com/u/85371542/EMail_14.trc

    https://dl.dropbox.com/u/85371542/EMail_15.trc

    https://dl.dropbox.com/u/85371542/EMail_16.trc

    https://dl.dropbox.com/u/85371542/EMail_17.trc

    https://dl.dropbox.com/u/85371542/EMail_18.trc

    https://dl.dropbox.com/u/85371542/EMail_19.trc

    https://dl.dropbox.com/u/85371542/EMail_20.trc

    https://dl.dropbox.com/u/85371542/EMail_21.trc

    Let me know what you find.

    Thanks

    Tom

    Thomas R Grassi Jr

    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:33 PM
    •  
     
  • Monday, October 15, 2012 9:52 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi TRGOneCare,

    Since you have several Maintenance Plans, please check these plans and make sure that there is no duplicate Notify Operator Tasks. Additionally, I suggest giving each Notify Operator Task one special identification to identify the email is sent from it. For example, we can add “notification01” , “notification02”, to “Notification message subject” of Notify Operator Tasks.

    Allen Li

    TechNet Community Support

    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:33 PM
    •  
     
  • Monday, October 15, 2012 1:07 PM
     
     Answered
    Sign In to Vote
    0

    Allen Li

    I do not have any Notify Operator Tasks defined in the maintenance plans.

    What I do have is each job that was created by the maintenance plan has this option in the properties.

    Under the Job Properties you slect a page "Notifications" then I checked EMail choose an operator  and the selected When a job fails

    This was done for every job created.

    I remember when I created the maintenance plans I selected the operator for the notify task.

    This is how each maintenance plan was setup.

    Any other ideas?

    Thomas R Grassi Jr

    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:33 PM
    •  
     
  • Monday, October 15, 2012 7:12 PM
     
     Answered
    Sign In to Vote
    0

    Hi

    did you get a chance to look at the ouput from the sql profiler? See my links below.

    Thanks

    Tom

    Thomas R Grassi Jr

    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:34 PM
    •  
     
  • Tuesday, October 16, 2012 11:02 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi,

    First, I want to inform that each sub plan of a Maintenance Plan will generate a SQL job, since you defined the steps to send email with each job, the total amount of the emails will equal to the job amount. For example, there are two Maintenance Plans (every of them has two sub plans), when Maintenance Plans run, they will send four (2*2) emails. If you only run one sub plan related job, only one email related to this job will be sent.

    Allen Li

    TechNet Community Support

    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:33 PM
    •  
     
  • Tuesday, October 16, 2012 12:46 PM
     
     Answered
    Sign In to Vote
    0

    Allen Li

    I am confused now. Why does every Manitenance Pan have two sub plans?

    Am I wrong?

    When you create a maintenance plan you select tasks that create a subplan

    If you schedule the tasks then one subplan is created for each task.

    This is the method I chose.

    When you save the maintenance plan the a job is created in sql agent for each task.

    I do not see any maintenance plan two sub plans. These are not duplicates.

    My maintenance plans have 5 or 6 subplans see below.

    AS posted above I selected the reporting and  logging method when I created the maintenance plan.

    Each subplan has a notification defined only if the job fails.

    From what I see this should not be sending duplicate emails for the same subplan. Thats what I am getting

    Any other thoughts or ideas?

    Thomas R Grassi Jr

    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:33 PM
    •  
     
  • Wednesday, October 17, 2012 10:46 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi, I want to explained that I assumed each Maintenance Plan has two subplans in my previous reply, of course, there may be different account subplans. With your Maintenance Plan, there are six subplans, so there will be six jobs generated like “DB Maintenance Plan Besx.Subplan_1”, “DB Maintenance Plan Besx.Subplan_2”, etc. Since you selected the option to send email with “reporting and logging”, this setting will be applied to all subplans. When any subplan fails, it will send an email to you. If there are four subplans failed, you will receive 4 emails. I suggest unchecking the option to send email with “reporting and logging”, and define a Notify Operator Task with different content for each subplan, if this way, if a subplan fails, we will receive a special message related to this failed subplan. For more detail information, please refer to the following document:

    Notify Operator Task:
    http://msdn.microsoft.com/en-us/library/ms140060.aspx

    Allen Li

    TechNet Community Support

    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:33 PM
    •  
     
  • Wednesday, October 17, 2012 1:23 PM
     
     Answered
    Sign In to Vote
    0

    Allen Li

    Yes this is correct I have 5 to 6 subplans and each subplan creates a job. The Option reporting and logging is on and this will send the log information in the body of the email. for each subplan that runs. If I use Notify task I will not get the log information needed. and then I would also have to add that to every subplan.

    Not sure if I am explaining things correectly here but the reporting and logging is what is not working properly.

    On a daily basis subplan5 runs for all the databases that have tranaction logs. Only on Sunday do I run all the subplan jobs.

    So now back to the daily jobs.

    Example.

    The BESX database has a transaction log so I am doing a transaction log backup every day except for Sunday when I do a full backup.

    subplan5 which is job DB Maintenance Plan BESX.Subplan_5  is the only job that runs.

    This send any where from one to 6 emails for this one job. This should only send one email per job.

    I understand if six subplan jobs run I will get six emails and thats what I want

    The problem is I am gettting duplicate emails for the exact same job

    Hope that clears up my problem

    Tom

    Thomas R Grassi Jr

    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:33 PM
    •  
     
  • Monday, October 22, 2012 8:24 AM
     
     Answered
    Sign In to Vote
    0

    1. try create a test Maintenance plan with only one sub plan

    2. capture screenshot of "notifications" about job 

    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:33 PM
    •  
     
  • Monday, October 22, 2012 12:55 PM
     
     Answered
    Sign In to Vote
    0

    FightAsBull

    I created a test Maintenance Plan with only one sub plan. Scheduled it to run and it ran successfully and only sent one email.

    Not sure about the screenshot of notifications you are looking for. Where do you mean?

    So that worked with one sub plan what does that mean? The idea of a maintenance plan is to schedule several tasks together If I can only do one in a maintenance plan then did I uncover a bug?

    What I am doing is suppose to work is that correct?

    What do you think I should do next?

    Thanks

    Tom

    Thomas R Grassi Jr

    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:33 PM
    •  
     
  • Thursday, October 25, 2012 9:59 AM
     
     Answered
    Sign In to Vote
    0
    Add another sub plan, test.

    if still work, add another until meetting your requirement
    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:33 PM
    •  
     
  • Thursday, October 25, 2012 4:02 PM
     
     Answered
    Sign In to Vote
    0

    FightAsABull

    I did just that added one sub plan at a time then tested and they all worked plus only one email this time.

    So why is it when I created a maintenance plan with all the sub plans do not work?

    Going to delete one of my maint plans and recreate it using same method and sees what happens

    Thomas R Grassi Jr

    Update

    I did another test. I updated all six jobs and from the job properties page selected notifications and selected email to operator and when job fails.

    Ran all the jobs from the schedule again and this time I got duplicates.

    So I removed the notifications and then ran them again and only one email

    So why is it when you add the notifications to email when a job fails you get mulitiples?


    I removed the notifications from every job created and will see what happens will post results.
    • Edited by TRGOneCare Thursday, October 25, 2012 6:17 PM
    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:33 PM
    •  
     
  • Tuesday, October 30, 2012 2:28 AM
     
     Answered
    Sign In to Vote
    0

    I removed the notifications from every job created and will see what happens will post results.


    what's the result
    • Marked As Answer by TRGOneCare Thursday, November 01, 2012 6:33 PM
    •