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

SQL Server Error:

Failed:(-1073548784) Executing the query "IF object_id('tempdb..#DatabaseList') IS NOT NULL

..." failed with the following error: "The index "IX_*" (partition 1) on table "*" cannot be reorganized because page level locking is disabled.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

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.

Resolution:

I have daily job that identify the index fragmentation and perform index reorganize or index optimization based on the level of fragmentation. The job was getting failed on the above error (first) and further drill down on the issue, I found that index rebuild was getting failed with the second error (shown in red).  It was very clean from the error that page level lock is prevented and SQL Server is unable to get lock on the page in order to modify index.

When I looked at the properties of the index, I found that "use page locks when accessing the index" was unchecked, which was preventing SQL Server to take lock on the index.

In order to allow SQL Server to take lock on the page, this option should be checked.  

 

Alternatively you can use the below script to allow page lock on the index. 

USE [Database Name]

GO

ALTER INDEX [Index Name] ON [dbo].[Table Name] SET ( ALLOW_PAGE_LOCKS  = ON )

GO