Index Issue

Index cannot be reorganized because page level locking is disabled error in SQL Server

"IX_PlacCheckHistoryLast3Months_DateCalled_Desc" (partition 1) on table "tblPlacCheckHistoryLast3Months" cannot be reorganized because page level locking is disabled. [SQLSTATE 42000] (Error 2552).  The step failed.

Error Message

Msg 2552, Level 16, State 1, Line 1 The index "Index_Name" (partition 1) on table "Table_Name" cannot be reorganized because page level locking is disabled

Solution

Before to set the page level locking  check other tables indexes option  using below query .

Select A.Name as IndexName,ob.Name as Tablename from sys.indexes A

left outer join sys.objects ob on ob.object_id=A.Object_id

where allow_page_locks=0 and ob.type='U'

A database administrator 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 SSMS

As shown in the below snippet select the check box Use page locks when accessing the index as highlighted and then save the changes before rebuilding the index.