Reindex of Table Stuck

Question

Reindex of Table Stuck

skb 44459

SSCarpal Tunnel

Points: 4768
More actions
December 21, 2016 at 5:36 am

#333084

We issue the following command to reindex the table.
DBCC DBREINDEX ('job', '', 80 )
Sometime this process is stuck until we resolve the blocking issue.
Is there a way to identify and kill all the blocking processes automatically when we run the reindex.
It is ok to do it because we will be running this In the night time and users are not working .

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

skb 44459 (12/21/2016)
We issue the following command to reindex the table.
DBCC DBREINDEX ('job', '', 80 )
Sometime this process is stuck until we resolve the blocking issue.
Is there a way to identify and kill all the blocking processes automatically when we run the reindex.
It is ok to do it because we will be running this In the night time and users are not working .

According to MS:

APPLIES TO: SQL Server (starting with 2008)

and

Important

This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use ALTER INDEX instead.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Shekhu Right there with Babe Points: 780 More actions · Answer 2

you can use sp_who2 to see all processes and check which SPID is actaully causing blocking and kill that... To automate that you can write take the results of Sp_who2 in a table and loop through the table to kill the blocking SPID's.

Another easier way to do this , is to take you database in single user mode before you start the reindex job. As you said you are doing it during maintenance window , you should be fine if people are not able to connect during that time. If you go by this approach rebuild index with Offline option so that it completes faster.