When you use the Maintenance Plan wizard in SQL Management Studio to set up a new plan, you have the option to have the SQL Agent send you a plan execution summary email:
It’s a pretty handy email – not just a success/failure on the SQL Agent job, but details about the actual maintenance plan steps and which ones ran and for how long. The problem we ran into was how to actually turn this email off once it was initially enabled in the wizard! It’s not anywhere in the SQL Agent job or in the properties of the maintenance plan, or even anywhere obvious in the maintenance plan designer. It’s hidden in a tiny button on the toolbar in the designer called “Reporting and Logging”:
Clicking there gets you some logging options, including sending a summary email to an operator:
From there, you can change the operator or stop the email summary (or start it, if that’s what you’re looking for).
My backup/maintenance plan recently started failing with the following (not so helpful) error message:
Source: Update Statistics
Description: The Execute method on the task returned error code 0x80004002
(Unable to cast object of type 'System.DBNull' to type 'System.String'.).
The Execute method must succeed, and indicate the result using an
It turns out the error was as a result of one of my databases being offline – the plan was set to work on all databases (the default), but there’s a checkbox that tells SQL Server to skip databases that aren’t online, and it’s not checked by default when you create your maintenance plan. Checking it solved the problem and the maintenance plan ran normally again.
To find the box, open your maintenance plan, and then in each task that runs in your databases, open the properties and click the dropdown, then check the box as in this picture: