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.


Job 'CobraTest.Subplan_1' failed. (SqlManagerUI)



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?

3 Answers 3


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

  • Hi, The procedure just does a single insert, so the only message was '1 row(s) inserted'. I've added 'SET NOCOUNT ON' to surpress this message, but the problem still occurs.
    – Gavin
    Jul 20, 2009 at 8:45

Have you opened the job history to view the error message? Make sure you expand the history entry and view the message from the actual job step.

A common causes for false negatives is Warnings during execution. For example, the old "Warning: Null value is eliminated by an aggregate ..." message often causes SSIS jobs to report failure (depending on the the setting chosen for the allowable number of warnings.)

If the actual SQL being run isn't dynamic, run it manually in a query window and check the results there.

  • Hi, I've gone down the history. There is only one step (as mentioned, T-SQL block, single insert, record is inserted). The message is as follows: Date 20/07/2009 09:29:47 Log Job History (MyDB.Subplan_1) Step ID 1 Server Server\PRODUCTION Job Name MyDB.Subplan_1 Step Name Subplan_1 Duration 00:00:06 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message Executed as user: Username. The package execution failed. The step failed. The sql is no dynamic, I can paste it into Query Editor and it runs with no warnings.
    – Gavin
    Jul 20, 2009 at 8:50

I don't have a definitive answer for this, but the server is running SQL Server 2005 with SP1. My client machine from which I was connecting using Managament Studio is running SP2. When I create the Maintenance Plan on the server (through Terminal Services), it runs successfully. When I create the Maintenance Plan on my machine (connected to the server), it fails.

This will require further investigation, because I would normally use that same client machine to work on both SP1 and SP2 servers, but in this instance that doesn't seem to be such a good idea.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .