July 15, 2009 at 4:31 pm
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
----------------------------------------------------------------------------
July 15, 2009 at 5:19 pm
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
July 16, 2009 at 9:03 am
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
----------------------------------------------------------------------------
July 16, 2009 at 9:22 am
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
July 16, 2009 at 9:50 am
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
----------------------------------------------------------------------------
July 22, 2009 at 9:32 am
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:
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
----------------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply