TryCatchFinally.net Some SQL, some .NET, and whatever else

22Feb/120

Failing maintenance plan on SQL Server 2005 when databases are offline

My backup/maintenance plan recently started failing with the following (not so helpful) error message:

Code: 0xC0024104
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
"out" parameter.

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: