We setup ALTER Index job with Ola's maintenance solution, the job is failing with below error, the job is failing with locking,
Best Answer
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:
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
The syntax error you are getting is from the $() this is taken as a token in SQL Server Agent context...so it will always bark at that; removing it should fix the syntax error.
On the copy issue you will likely need to specify the property you want to pass into Copy-Item. Just passing $myfile is not going to work because that contains the full object of the output from gci. So change it to something like:
It sounds like you are using Availability Groups and either one of your replicas is not syncing correctly, or it is getting so far behind that it is causing the log on your primary to grow until is reaches its maximum size (or you run out of disk).
Check that your secondary replica(s) are online and syncing
correctly.
See if you can increase the max size of your transaction
log on the primary
Increase the allocated storage on your transaction
log drive
Rebuild individual indexes with a wait between them to help
allow time for log blocks to flush down to your secondary replica(s) (check out Minion Reindex free tool that can help with this)
Best Answer
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:
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 yourALTER 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 theALTER INDEX
command from acquiring its lock. At that point, you have a bunch of options to deal with the problem: