Hi,
I have a maintenance plan to rebuild all the indexes on a specific database at 03:00 am.
The rebuild tasks fails with the following error
Executing query "ALTER INDEX [TheCaptStepPK] ON [dbo].[... The package execution fa... The step failed.
SQLAgent service is configured to run under the SQLService account
The SQLService account has local admin on the server and is a SQL Server system administrator account.
If I script our the maintenance plan and run the TSQL logged in to the server under the SQLService account under which this job is scheduled to run, the task completes with success
If I go to the index which is failing using enterprise manager (again logged in under the SQLService account) and rebuild the index manually then the task completes with success
If I run the job from SQL Agent (right click & run) then the job fails at the same point.
If anyone has any suggestions as to why this is failing I'd appreciate to hear.
If I create a SQL Server Agent Job using the same code as generated by the maintenance plan, then the task completes with success.
In the interests of simplicity I would to avoid the use of TSQL scripts and just use a maintenance plan but this one particular process seems to fail consistently.
Maintenance plans are in place for backups, transaction log file shipping and database and old log file clean ups.
Has anyone else had a similar issue or got any advice as to what is causing this problem?