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

Ekrem Önsoy

5 years ago

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:

SELECT A.Name AS InName,ob.Name AS DBName 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'

SELECT A.Name AS InName,ob.Name AS DBName 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'

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:

ALTER INDEX  index_name ON table_name SET (ALLOW_PAGE_LOCKS = ON)

1	ALTER INDEX index_name ON table_name SET (ALLOW_PAGE_LOCKS = ON)