Help for rebuild index

  • In one of my production server scheduled a maintenance plan for rebuild index on 1 am.

    suddenly i'm getting this error:

    Here is the error

    Executing the query "ALTER INDEX [work_package_calendar_work_package_IDX] ON [dbo].[work_package_calendar] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )

    " failed with the following error: "Transaction (Process ID 86) 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.

    Any suggestions please urgenttttttttt

  • Set ONLINE = ON in the above script , the rebuild index is acquiring locka dn making the table unavailable hence the error

    Jayanth Kurup[/url]

  • subbkarth (7/14/2011)


    In one of my production server scheduled a maintenance plan for rebuild index on 1 am.

    suddenly i'm getting this error:

    Here is the error

    Executing the query "ALTER INDEX [work_package_calendar_work_package_IDX] ON [dbo].[work_package_calendar] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )

    " failed with the following error: "Transaction (Process ID 86) 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.

    Any suggestions please urgenttttttttt

    What edition of SQL Server are you using?

    what else is scheduled to run at 1am?

    try changing the scheduled time to fit around other activities.

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Maintenance plan is the wrong tool for this. It rebuilds all the indexes regardless of the fragmentation.

    This works about 200 times faster on my servers (after a few runs) :

    http://sqlfool.com/2010/04/index-defrag-script-v4-0/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply