I'm having a problem with a maintenance plan in SQL Server 2005. It's a very basic maintenance plan – it has one 'Execute T-SQL Statement Task' which simply inserts a row into a table.
When I execute the maintenance plan the T-SQL statement is executed and the row is entered into the table, but I get a message box displaying the following:
TITLE: Execute Maintenance Plan
Execution failed. See the maintenance plan and SQL Server Agent job history logs for details.
ADDITIONAL INFORMATION:
Job 'CobraTest.Subplan_1' failed. (SqlManagerUI)
BUTTONS:
OK
This happens when I execute the maintenance plan manually in SQL Server Management Studio by going Management –> Maintenance Plans –> Right Click –> Execute and also when I schedule it through SQL Server Agent.
There are other Maintenance Plans on the server which run ok both manually and scheduled.
One thing that I'm curious about is that the scheduled SQL Server Agent Job was running ok, then failed for a few nights, then ran ok for a few nights and is now failing again and there has been no intervention or changes to the SQL server which makes me think it could be someone changing things elsewhere on the network?
Any suggestions?
Best Answer
Random thought...
Is the exit code for the stored proc non-zero? For example, the RETURN statement gives @@ROWCOUNT or similar.
I saw similar a long time ago with SQL agent jobs... pre SSIS though