1

We have a SQL Server 2014 enterprise cluster. Server 1 and Server 2. SQL Server is limited to 64GB of memory.

We are using Ola Hallengren scripts for index rebuilding and optimizations once per week for all our databases.

Every time script triggers, it sometimes hangs on the same index

ALTER INDEX [SecretId] ON [someDB].[dbo].[Articles] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)

This is unique identifier field in the database. Table has approx 1.5 mil records.

When index rebuild hangs, we do a failover to another SQL server, restart service on first one, and failover back to instance one. After that, we re-run scripts for index rebuilding, and it passes well in a matter of minutes.

Why do we need to restart service in order to rebuild index successfully?

  • 2
    What type of wait do you see when it "hangs"? Is it being blocked? What makes you determine that it's hung and not doing work? Also, can you clarify if this is a Failover Cluster Instance or an Availability Group? Your "Failover-restart-failback" description makes me think it's an AG... – AMtwo Nov 4 '18 at 12:10
  • Is this a table with a large varbinary or nvarchar(max) column? I've seen unusual maintenance behaviors with these kinds tables. – Paul Williams Apr 5 at 13:13
0

Check the dbo.CommandLog table to see what the error is (for IndexOptimize)?

If it's lock timeout (from blocking), then obviously failover would resolve it as there shouldn't be blocking after a restart

You can also run sp_whoisactive to find the Head blocker

In my work's cases, sometimes even retry after 1 minute or 10 minutes would work (no need to failover) as the BLOCKING session has cleared/released the lock

But sometimes it'd still fail after retrying for an hour (inside SQL Job)

Let us know how it turns out

Good luck

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.