The index “index_name” (partition 1) on table “table_name” cannot be reorganized because page level locking is disabled

ERROR MESAGGE:

“Executing the query “ALTER INDEX [index_name] ON [dbo].[table_name] REORGANIZE WITH ( LOB_COMPACTION = ON ) ” failed with the following error: “The index “index_name” (partition 1) on table “table_name” 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.”

EXPLANATION:

You may encounter an error message when you reorganize an Index.

SOLUTION:

The reason you are getting this error is that the ALLOW_PAGE_LOCKS property of the corresponding Index is turned off.

You can run the following query to see the indexes with ALLOW_PAGE_LOCKS turned off in your database:

You can enable the ALLOW_PAGE_LOCKS property of an Index with SSMS or with T-SQL:

With SSMS: Right-click on the Index in the table, select “Properties” from the drop-down menu and select “Use page locks when accessing the index” in the Options section of the pop-up window and click OK.

With T-SQL: Run the following command for the corresponding index: