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:
1 2 3 | 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:
1 | ALTER INDEX index_name ON table_name SET (ALLOW_PAGE_LOCKS = ON) |