Maintenance Plan Job Failing, but MP History Reporting Success?

SQL Server Tools General

Phil Pawlusiak


SQL Server 2005 (9.00.3054) x64
Windows Server 2003 x64 SP2

I have created a maintenance plan in SQL 2005 SP2 with three subplans. In SQL Server Agent Job Activity Monitor, I see all of the jobs are failing.

My transactional backup subplan, which only does transactional backups, is failing with:
Executed as user: DB01\DB01SQLService. 15:02 AM Elapsed: 1.937 seconds. The package execution failed. The step failed.

The user DB01SQL Service is a local admin on the server. I know this is not recommended, but I'm trying to get rid of these errors.

My daily backup subplan, which performs full backups of all user databases after it does integrity checks and cleans up old .bak and .trn files, is failing with:
Executed as user: DB01\DB01SQLService. ...plete End Progress Progress: 2007-06-18 00:00:03.25 Source: Maintenance Cleanup Task (bak) Executing query "EXECUTE master.dbo.xp_delete_file 0,N'G:\MSSQL\BAC".: 100% complete End Progress Progress: 2007-06-18 00:00:04.83 Source: Check Database Integrity Task Executing query "USE [V2_Old] ".: 50% complete End Progress Progress: 2007-06-18 00:01:46.97 Source: Check Database Integrity Task Executing query "DBCC CHECKDB WITH NO_INFOMSGS ".: 100% complete End Progress Progress: 2007-06-18 00:01:46.97 Source: Check Database Integrity Task Executing query "USE [V2Utility] ".: 50% complete End Progress Progress: 2007-06-18 00:01:48.13 Source: Check Database Integrity Task Executing query "DBCC CHECKDB WITH NO_INFOMSGS ".: 100% complete End Progress Progress: 2007-06-18 00:01:48.13 Source: Check Database Integrity Task Executing query "USE [V2_IBM] ... The package execution fa... The step failed.

My daily maintenance subplan, which updates statistics and reorganizes indexes, is failing with:
Executed as user: DB01\DB01SQLService. ...1:03.01 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[ActiveSPParameter] WITH".: 0% complete End Progress Progress: 2007-06-16 22:21:03.01 Source: Update Statistics Task Executing query "use [V2_Old] ".: 0% complete End Progress Progress: 2007-06-16 22:21:03.08 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[ActiveSPPeriod] WITH FU".: 0% complete End Progress Progress: 2007-06-16 22:21:03.08 Source: Update Statistics Task Executing query "use [V2_Old] ".: 0% complete End Progress Progress: 2007-06-16 22:21:03.27 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[ActiveSPTransaction] WI".: 0% complete End Progress Progress: 2007-06-16 22:21:03.29 Source: Update Statistics Task Executing query "use [V2_Old] ".: 0% complete End Progress Progress: 2007-06-16 22:21:03.4... The package execution fa... The step failed.
Note that I have executed UPDATE STATISTICS [dbo].[ActiveSPPeriod] WITH FULLSCAN without any errors.

I have looked in the log files for each of these job runs and the logs say they have completed successfully. When I right click on the maintenance plan and select View History, each of the subplans indicate they were executed successfully. However, when I look in the Windows event log I find very generic errors saying that the maintenance plan failed.

So the logs and maintenance plan history indicate successful execution and job history and Windows event viewer indicate failure of those same tasks.

Could somebody give me a hint as to what the problem is



Top

Re: Maintenance Plan Job Failing, but MP History Reporting Success?

LisaInMI


I have a very similar problem, also using SQL Server 2005 SP2 and Windows Server 2003 SP2, with a mainteance plan that does a full database backup on the weekends. It has failed the last two weekends on the first step, which is the Check Database Integrity task, with the following message:

Message
Executed as user: DBServer\sqlsvc. ...e End Progress Progress: 2007-07-01 01:00:11.91 Source: Check Database Integrity Executing query "USE [GPNEU] ".: 100% complete End Progress Progress: 2007-07-01 01:00:12.86 Source: Check Database Integrity Executing query "DBCC CHECKDB WITH NO_INFOMSGS ".: 100% complete End Progress Progress: 2007-07-01 01:00:12.86 Source: Check Database Integrity Executing query "USE [GPNW] ".: 100% complete End Progress Progress: 2007-07-01 01:00:13.83 Source: Check Database Integrity Executing query "DBCC CHECKDB WITH NO_INFOMSGS ".: 100% complete End Progress Progress: 2007-07-01 01:00:13.84 Source: Check Database Integrity Executing query "USE [GPVSI] ".: 100% complete End Progress Progress: 2007-07-01 01:00:15.33 Source: Check Database Integrity Executing query "DBCC CHECKDB WITH NO_INFOMSGS ".: 100% complete End Progress Progress: 2007-07-01 01:00:1... The package execution fa... The step failed.

I have also checked logs files, event viewer, job history and can find nothing but the generic message: The package execution failed. I cannot even find what step the package is failing on. When I run just the first step (the Check Database Integrity step) by itself, it executes successfully.



Top

Re: Maintenance Plan Job Failing, but MP History Reporting Success?

Phil

So are all of the subplans reporting successful execution in the Log File Viewer Also, I am assuming that the backups are actually being created on your weekly schedule if you look on your backup drive.

One thing I thought to check after reading your response was to make sure that the order of precedence links in each of the subplans are set to continue to the next task on success. I confirmed this is how the plans have been set up, so my plan that checks integrity and performs backups should never get to the backup task since the integrity check is failing.

Do you have a similar setup






Top

Re: Maintenance Plan Job Failing, but MP History Reporting Success?

LisaInMI

No, the backups were not actually being created because the integrity check step failed and the link to the next step was set to continue on success. Last week I changed the link to continue on completion (just to get the backup task to run) and the backup ran successfully this weekend! Also, I did not get any errors this weekend on the integrity check task. So I'm not sure if there are two different things going on here. Up until three weekends ago I was not having any problems with this maintenance plan job. I might try changing the integrity check task link back to continue on success and see what happens next weekend.


Top

Re: Maintenance Plan Job Failing, but MP History Reporting Success?

James K. Howey - MSFT

I am interested in understanding what is going on here.

If your experiment yields any conclusions, please keep us posted.

You can use profiler to capture the TSQL executed by these tasks and run it in query designer to see if the commands fail outside the context of the maintenance plan.

jkh





Top

Re: Maintenance Plan Job Failing, but MP History Reporting Success?

Phil

I ran the profiler during the scheduled execution of the maintenance plan, but I did not find a BACKUP LOG statement being executed on either the SP StatementCompleted or SQL StatementCompleted events. What events should I be monitoring to capture the backup log statement executed by the maintenance plan



Top