SQL maintenance plan fails on rebuilding indexes.

Question

SQL 2008 R2 maintenance plan fails on the first step - Rebuild index.

Error code:
Executed as user: EFACSSERVER\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.50.4042.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 00:30:00 Progress: 2022-09-25 00:30:01.34 Source: {913460B6-BFBF-42D5-806F-BF7E103FF087} Executing query "DECLARE @George Wang UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Error: 2022-09-25 00:31:34.51 Code: 0xC0024104 Source: Rebuild Index Description: The Execute method on the task returned error code 0x80131501 (An exception occurred while executing a Transact-SQL statement or batch.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error Progress: 2022-09-25 00:31:34.52 Source: Notify Operator Task 2 Executing query "EXECUTE msdb.dbo.sp_notify_operator @DeezNutz =N'Chris ...".: 100% complete End Progress DTExec: The package execution returned DTSER_FAILURE (1). Started: 00:30:00 Finished: 00:31:34 Elapsed: 94.162 seconds. The package execution failed. The step failed.

Does anyone have any clue what is going wrong?

Thank you,

Sham

Answer 1

Can you review the maintenance plan history (right-click on the maintenance plan in Object Explorer and View History).
From there you should be able to see what task failed and the actual error. Can you then paste that in here for review?

Answer 2

It doesn't tell anything, see screenshots below:

Answer 3

Hi @ShamVMH

• It may be due to insufficient permission to execute Index Rebuild command on the database by the user in this case its executed as Executed as user: EFACSSERVER\SYSTEM.
  As you can see there are some hints about it in the error you posted for example this line : The package execution returned DTSER_FAILURE (1).
  and this line : Code: 0xC0024104 Source: Rebuild Index Description: The Execute method on the task returned error code 0x80131501 (An exception occurred while executing a Transact-SQL statement or batch.).
  Rebuild is an ALTER command so even if the job is running the step is trying to execute the plan which has that command in the logic and failing.
  There is some related content on internet about the same problem faced by other users and they all points to permission issue on databases for account which the job was getting executed as.
  • Long story short try Running this job as user with sufficient permission if this account already has the permissions see next point.
  • Another problem could be the shortage in log space during the operation so if its failing after running for a while and not instantly make sure your logs are able to grow and disk has enough space.

Answer 4

Hi @ShamVMH ,

Please check the current user account has correct permissions

Please check out this thread:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ce5068f7-087a-49bc-bf19-b4ac78269729/index-maintenance-plan-succeeded-but-sql-agent-job-history-shows-fail?forum=sqldatabaseengine

Try to switch away from built in maintenance plans to a script based method, to find the error, you'd better catch the error, and you may need to track the activity during execution to find the failed statement

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Thank you all for your suggestions.

I will read through them and try implementing them.

I will let you know if it worked.

Thank you,

Sham