Rebuild Index maintenance task failed due to deadlocks.

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

October 3rd, 2013 3:21pm

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

Free Windows Admin Tool Kit Click here and download it now
October 3rd, 2013 3:37pm

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

October 3rd, 2013 3:46pm
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 r

Free Windows Admin Tool Kit Click here and download it now
October 3rd, 2013 4:07pm

The cause for deadlock is some other transaction is having lock on the desired table.

As index rebuild requires exclusive lock on the table so operation will continue waiting until it gets the exclusive lock on the object.

You need to perform this activity in a window where no other transaction could block it. Please set up proper monitoring to control any blocking to index rebuilding activity.

There could be possibility to bring database in single user mode so that no other user can not this database and then you perform index rebuild.

But please do not perform it on production before taking due diligence on database concurrency and availability impact. 

February 20th, 2015 12:30pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics