Enabling page level locking

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.