none
Rebuild Index maintenance task failed due to deadlocks.

    Question

  • Hi All,

    We have maintenance plan which rebuilds fragmented indexes, this task is executing successfully for the past 3 months on weekly basis but last week this task is failed with the below given error: 

    "Executing the query "ALTER INDEX [IX_ProductSettingValues_LastUpdate..." failed with the following error: "Transaction (Process ID 108) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

    How to resolve this issue and make it run successfully, grateful to your suggestions on this.

    Regards,

    Shiva


    ----Learners Curiosity Never Ends----

    Thursday, October 03, 2013 12:21 PM

Answers

All replies

  • This is a typical Deadlock error message. It looks like some other transaction is running on the system which touches the ProductSettingValues. Index Rebuild are not supposed to run on business hours. Please schedule at off peak hours to avoid such issues.

    Also, you can use Ola's script:

    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, October 03, 2013 12:37 PM
  • Hi All,

    We have maintenance plan which rebuilds fragmented indexes, this task is executing successfully for the past 3 months on weekly basis but last week this task is failed with the below given error: 

    "Executing the query "ALTER INDEX [IX_ProductSettingValues_LastUpdate..." failed with the following error: "Transaction (Process ID 108) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

    How to resolve this issue and make it run successfully, grateful to your suggestions on this.

    Regards,

    Shiva


    ----Learners Curiosity Never Ends----

    Are you running Enterprise edition? If so, you can rebuild indexes Online.

    Thursday, October 03, 2013 12:46 PM
  • Are you running Enterprise edition? If so, you can rebuild indexes Online.

    Hello Shiva,

    Even online index rebuild takes two shorterm locks and I agree with Latheesh Index rebuild is maintenance task and should be run during maintenance window or when load is less.Script from Ola wil help you.

    Also please rebuild only on basis of fragmentation >30 % else reorganize.

    I strictly oppose scheduling Index rebuild on OLTP database using maintenance plan where all indexes are rebuilt


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Thursday, October 03, 2013 1:07 PM