How to troubleshoot problems that occur when you use a Database Maintenance plan in SQL Server (288577)

SUMMARY

If any task in a Database Maintenance plan fails or encounters an error message, the maintenance plan job itself may show as failed. The Job History Details shows the following error message:

sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

However, this does not necessarily indicate that the Maintenance Plan as a whole has failed. The Maintenance Plan job shows a status of failed in order to raise a flag to the user that a task in the maintenance plan requires attention.

This kind of behavior is normal because errors may be encountered that need user intervention to work through. Any time a maintenance plan job fails, you must check the plan history to see what the failure is and then take the appropriate action to resolve the problem.

MORE INFORMATION

To identify the specific task and error message about why the job failed, you need to check the Database Maintenance Plan History. The Database Maintenance Plan History is where you can obtain detailed information about each task that is being performed by the maintenance plan. Use these steps to locate the information in the SQL Server Enterprise Manager:

Navigate to and then expand Database Maintenance Plans.
Right-click the Database Maintenance Plan for which the corresponding job failed.
Click Database Maintenance Plan History.
A window opens that contains details about each task performed in the Maintenance Plan. A historical list is provided for each task run on each date, which also indicates the failure or success status of the individual task. If you double-click a failed task, information displays indicating any error messages that occurred along with information about why the task failed.
Take the necessary steps at this point to resolve the specific issue indicated.

A typical example is a Maintenance Plan that is set up to backup databases and to check integrity. For example, assume that one of the integrity check tasks encounters data integrity errors in one or more databases. The task raises an error message. The Maintenance Plan continues with the rest of the tasks in the plan. When the Maintenance Plan completes, the corresponding job in SQLAgent is set to a status of "failed". A review of the Maintenance Plan History indicates which database integrity check found integrity errors or failed and the corresponding error messages. You then need to take action and run the appropriate DBCC CHECKDB steps to resolve the issue. For more information about DBCC CHECKDB, see the "DBCC CHECKDB" topic in SQL Server Books Online.

You can also have detailed Maintenance Plan information written to a file. To have detailed Maintenance Plan information written to a file, use these steps:

In SQL Server Enterprise Manager, navigate to the Database Maintenance Plans. Select and then double-click the plan you want.
Click the Reporting tab. In the Reporting dialog box, you can choose to write a report to a text file in a specified directory. The report contains details of the steps executed by the maintenance plan, and includes any error information. You can also have this report e-mailed to a specified operator for regular checking.
Instead of a text report, you can direct the output to a SQL Server table with the History on this server option.

For more information about the details of setting these options for a Database Maintenance Plan, see the "Database Maintenance Plan, Reporting Tab" topic in SQL Server Books Online.

If you need more in-depth troubleshooting, you can start SQL Server Agent to run from the command prompt with verbose logging.

Default instance:

sqlagent.exe -c -v

Named instance:

sqlagent.exe -i[Instance] -c -v