Here are a few possible solutions to your problem:
Option 1: Don't rebuild that index
That's a 30 GB index you have there. What measurable performance problem are you trying to solve by rebuilding it? Especially at 5% fragmentation, this seems like an incredibly expensive operation (in terms of system resources and locking) for very little gain.
You can read some very well-founded opinions on why you might want to give up on the index rebuild here:
- Stop Worrying About SQL Server Fragmentation
- Good Reasons to Rebuild or Reorganize Indexes
- Video: Why Defragmenting Your Indexes Isn’t Helping
This is far and away your best, and EASIEST, option. This is the home run. Do this.
Option 2: Rebuild online
Index rebuilds require a SCH-M lock. If you add
WITH (ONLINE = ON) to your
ALTER INDEX command, that lock will be deferred until the very end of the rebuild operation, which might increase the potential that whatever is preventing your maintenance task from completing has released its locks.
Option 3: Identify the blocking query
This is probably the most work of the options. You can run sp_WhoIsActive while the
ALTER INDEX command is running, and it should show you what else is running, and specifically it will show you what other session is blocking the
ALTER INDEX command from acquiring its lock. At that point, you have a bunch of options to deal with the problem:
- reschedule your index maintenance so it's not running at the same time as the blocking query
- re-work your blocking query so that it takes less locks / different locks / holds locks for less time (you could post another question with execution plan details if you need help with that)
- reschedule the blocking query so that it is not running during the period when you do index maintenance