TSQL to reindex an index only after the reindexing the previous index finishes?!

• WebTechie

  SSChampion

  Points: 11405

  More actions

  August 25, 2014 at 2:14 pm

  #295757

  Hello,

  I have a vendor database (Solarwinds) that has over 2000 indexes. Some of the nights the reindexing job runs, it executes successfully. But most of the time, the job fails.

  When I check the logs, the reindexing is trying to reindex an index while the table still has a lock on it. The previous index may still be rebuilding and has a lock on the table. When the code tries to rebuild the next index, the job is failing.

  Therefore, I need to script a stored procedure that will loop through all 2000 indexes doing the following:

  1) Gets the name of the index

  2) Get the table associated with the index

  3) Check that there is not a lock on the table

  4) Reindex that index

  5) Proceed to the next index

  I can write a script that will loop through sys.indexes where type = 1 or 2.

  However, what is the code to see if a table is available or if it still has a lock on?

  Error Message:

  Executed as user: Transaction (Process ID 67) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205). The step failed.

  Things will work out.  Get back up, change some parameters and recode.