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?