Index Maintenance plan succeeded but SQL Agent job history shows fail...

Question

HI guys,

  i am working with weird situation. 

I did setup Maintenance plan for Index Reorganize and Statistics Update. After job finished in SQL agent job it shows fail but Maintenance plan History shows succeeded .Below is error detail from SQL agent.

Error: Code: 0xC0024104     Source: Reorganize 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.

Any idea what are the steps to be taken to solve this?

Thank you...

---

Ankit Shah SQL Server DBA

Answer 1

The best thing would be to switch away from built in Maintenance Plans to a script based method that gives you better logging like the one provided free by Ola Hallengren.

http://ola.hallengren.com/

To continue with the built in Maintenance Plan, you'll need to have it write the output information to a file for the job step to trap the error, and you may need to trace the activity during the execution to find the statement that is failing.  Do you have any error handling setup in the Maintenance Plan?  Can you post a screenshot of your plan definition?

---

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

Answer 2

Verify the version of SQL Server and the version of your client tools.  You can see this issue if you build the maintenance plan with a client system that is on a lower version than the server.

---

Jeff Williams

Answer 3

This should be one off error message which occurs sometimes. I have noticed this on Maintenance plans sometimes on Backup jobs. I have noticed that whenever this happened, the server was really busy with high CPU and memory usage from Data Collector sets on my server.Is that the same for you?

In maintenance plan history, have you Checked individual steps history to see all of them succeeded.

As Jonathan suggested, switching to script based method is a good suggestion which works intelligently based on Fragmentation percentage, decides whether a Rebuild is better or Re-organise.

Always test script based method on test server first.

Hope this helps......

---

Vamshi SQL DBA(MCITP) My Blogs: http://sqlserver-dba.co.uk http://sql-developers.co.uk/