Maintenance plans and email

  • I inherited some SQL Servers and need to change the nightly notification email for my maintenance plans but am thoroughly confused. There are two operators setup but when I look at their history, they both show they've never been emailed. The two operators are an email distribution list and myself. My task is to change the nightly notification from the dist. list to me.

    I looked in the 'Reporting and Logging' portion of the maintenance plan and changed the 'Agent Operator' to me, but have yet to receive an email.

    I can successfully email from that server to myself.

    Am I missing a step? The confusing part is the history of the two operators showing nothing.

    The email that was being sent is a nice listing of all the steps in the plan (there's multiple steps in 1 plan):

    NEW COMPONENT OUTPUT

    Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.4035 Report was generated on "server1".

    Maintenance Plan: Applications

    Duration: 00:28:38

    Status: Succeeded.

    Details:

    Back Up Database (Transaction Log) (server1) Backup Database on Local server connection Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.

    Databases: All user databases

    Type: Transaction Log

    Append existing

    Task start: 2009-07-15T02:27:45.

    Task end: 2009-07-15T02:28:55.

    Success

    Maintenance Cleanup Task 1 (server1)

    Maintenance Cleanup on Local server connection Cleanup Database Backup files

    Age: Older than 3 Days

    Task start: 2009-07-15T02:28:59.

    Task end: 2009-07-15T02:28:59.

    Success

    Update Statistics (server1)

    Update Statistics on Local server connection Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.

    Databases: All user databases

    Object: Tables and views

    All existing statistics

    Task start: 2009-07-15T02:08:02.

    Task end: 2009-07-15T02:17:16.

    Success

    Maintenance Cleanup Task (server1)

    Maintenance Cleanup on Local server connection Cleanup Database Backup files

    Age: Older than 3 Days

    Task start: 2009-07-15T02:28:55.

    Task end: 2009-07-15T02:28:59.

    Success

    Clean Up History (server1)

    Cleanup history on Local server connection History type: Backup,Job,Maintenance Plan

    Age: Older than 1 Weeks

    Task start: 2009-07-15T02:17:20.

    Task end: 2009-07-15T02:17:24.

    Success

    Check Database Integrity (server1)

    Check Database integrity on Local server connection Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.

    Databases: All user databases

    Include indexes

    Task start: 2009-07-15T02:00:24.

    Task end: 2009-07-15T02:05:00.

    Success

    Back Up Database (Full) (server1)

    Backup Database on Local server connection Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.

    Databases: All user databases

    Type: Full

    Append existing

    Task start: 2009-07-15T02:17:25.

    Task end: 2009-07-15T02:27:44.

    Success

    Clean Up History (IRTSQL)

    Cleanup history on Local server connection History type: Backup,Job,Maintenance Plan

    Age: Older than 1 Weeks

    Task start: 2009-07-15T02:17:20.

    Task end: 2009-07-15T02:17:24.

    Success

    Check Database Integrity (IRTSQL)

    Check Database integrity on Local server connection Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.

    Databases: All user databases

    Include indexes

    Task start: 2009-07-15T02:00:24.

    Task end: 2009-07-15T02:05:00.

    Success

    Back Up Database (Full) (IRTSQL)

    Backup Database on Local server connection Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.

    Databases: All user databases

    Type: Full

    Append existing

    Task start: 2009-07-15T02:17:25.

    Task end: 2009-07-15T02:27:44.

    Success

    TIA

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • I believe what you are seeing is the report created from the maintenance plan. Open the maintenance plan and click the little button to the right of manage connections. This should open up the Reporting and Logging page for the maintenance plan.

    On that page there is the option to send a report to an email recipient, which I am betting has been checked and the specified operator selected.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Thanks Jeff. But as I stated, that area was changed to email me and I've yet to see any email. Also, when I look at the properties of the job itself, there's an area under Notifications to Email, with some options. I entered myself there and am receiving the email when the job completes, but it's not the nice report from the maintenance plan.

    The Database Mail log shows that the mail process fires up, stays on for 10 mins (as set in the Database Mail properties) then goes away. No errors or detail on who was emailed, or if there were any errors.

    Wonder if there's any other ideas?

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • Well, the only other thing I think it could be is that the operator is not set to the right email address. Try executing sp_notify_operator to test the operator setup.

    Other than that, verify that SQL Server Agent Alert system has been configured to use database mail.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Could be a bug:

    http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/6f143ca9-d08c-4028-9fef-58ae62ae5fc6

    I'm going to follow up with Microsoft.

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • I found that the email account that I was using to try and send email was not configured properly. Found this out by talking to my email admin. We were using a "service" account that was created to mimic the distribution list:

    my-group-list@myemail.com

    it wasn't allowed to send mail, by default. Don't know if this was our policy or an Exchange policy.

    Caveat though: I recreated the maintenance plans from scratch, after being frustrated with the problem, so don't actually know if they could have just been modified, or if a completely new maintenance plan was necessary.

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply