SQL Server 2012 ALTER Index Job failing

We setup ALTER Index job with Ola's maintenance solution, the job is failing with below error, the job is failing with locking,

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

StackExchange Database Administrators Q#207405, answer score: 6