Categories
Sql Server

Index cannot be reorganized because page level locking is disabled

Index cannot be reorganized because page level locking is disabled

Hi Friends, recently my Index reorganize job is failed. When I checked about error: “The index “cndx_PrimaryKey_AsyncOperation” (partition 1) on table “AsyncOperationBase” cannot be reorganized because page level locking is disabled.”. 

Solution: A DBA can resolve the above error message by enabling Page Level Locking on the index and then reorganize the index.

Enable page level locking on index using SQL Server Management Studio:

Open SQL Server Management Studio, navigate to your Database > Table Name > Indexes > Right Click on Index name on which error you had received and go to Properties click on Options. Now check the option Use page locks when accessing the index.

Index cannot be reorganized because page level locking is disabled

Save the changes before reorganizing indexes.

Enable page level locking on index using TSQL:

ALTER INDEX [PK_cart_master] ON [dbo].[cart_master] SET ( ALLOW_PAGE_LOCKS = ON )