Ugly Bug: SQL Server Online Index Rebuild Sometimes Happens Offline Without Warning
🔥 UPDATE: This issue has now been documented. A note has been added to the Perform index operations online documentation page, stating: "Index rebuild commands might hold exclusive locks on clustered indexes after a large object column is dropped from a table, even when performed online."
I found a nasty bug in SQL Server and Azure SQL Managed Instance recently: sometimes an ‘online’ index rebuild of a disk-based rowstore clustered index (basically a normal, everyday table) isn’t actually ‘online". In fact, it’s very OFFLINE, and it blocks both read and write queries against the table for long periods.
If you manage to make it through a rebuild successfully, the problem goes away for future rebuilds of that clustered index – likely leaving you bruised and bewildered.