Lock request time out on Rebuild of index

Question

Lock request time out on Rebuild of index

Kwisatz78

SSChampion

Points: 12478
More actions
December 14, 2010 at 8:06 am

#144022

Hi all
I have a clustered index rebuild that runs weekly but more often than not fails on a particular clustered index. The table is quite large and is heavily fragmented.
However I keep getting a lock request time out whenever I try to run the ALTER INDEX REBUILD command.
Does anyone have any suggestions about how I can get this command to complete?
Thanks

Viewing 6 posts - 1 through 6 (of 6 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

You need to kill the process that is already accessing that index. This may just be a case of stopping your application while you perform the index rebuilds.

John

Kwisatz78 SSChampion Points: 12478 More actions · Answer 2

Hi thanks for the reply. How can I see whats currently accessing that index? I am having a mental block on this.

Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

kill the index job and then restart on offline hours.

sp_who2 to see who's blocking you (your spid is visible if the query window of SSMS).

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Or is there any ways to run a reorganize daily so that is splits the load?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

While the index rebuild is running, execute sp_who2 a few times. If the same SPID appears in the BlkBy column every time, and the SPID being blocked has ALTER INDEX in the Command column, then you have found the process that's stopping your index from being rebuilt. You can use DBCC INPUTBUFFER to find out what it's running.

John