Sometimes index rebuild task will be failed due to the following issue:
Description: Executing the query "ALTER INDEX [IDX_XXX_OF_DATE] ON [dbo].[DI..." failed with the following error: "The index "IDX_XXX_OF_DATE" (partition 1) on table "XXXX_OF_CF" cannot be reorganized because page level locking is disabled.".
Solution
Enable page level locking…
To find the disabled page level locking indexes, please execute the given query:
SELECT * FROM sys.indexes WHERE allow_page_locks = 0
Copy the index names from the above output and enable locking by executing the below query:
Alter Index <index name> On <table name> Set (ALLOW_PAGE_LOCKS = ON)
If we want to do the same for multiple indexes in single database at a time, please execute the following:
Use <dbname>
GO
select 'alter index ['+s.[name]+'] on ['+t.[name]+'] Set (ALLOW_PAGE_LOCKS = ON);' from sys.indexes as s inner join sysobjects as t on s.[object_id] = t.[id] where s.[allow_page_locks] = 0 and is_unique = 0
Copy the given OUTPUT and paste it into new NEW QUERY WINDOW and execute it.
Check for disabled indexes and re-run the job.
