The index cannot be reorganized because page level locking is disabled

Executing the query “ALTER INDEX [cndx_PrimaryKey_WorkflowLog] ON [dbo]…” failed with the following error: “The index “cndx_PrimaryKey_WorkflowLog” (partition 1) on table “WorkflowLogBase” cannot be reorganized because page level locking is disabled.

Summary:

Recently one of my server index reorganize job start failing. I checked the index name and then enable the page level locks on that particular index by following query:

ALTER INDEX [PK_cart_master] ON [dbo].[cart_master] SET ( ALLOW_PAGE_LOCKS = ON ) or follow this tutorial https://www.hightechnology.in/index-cannot-be-reorganized-because-page-level-locking-is-disabled/

, but again i got an error this time with a different index. So i build a TSQL query to find out all indexes and their respective table name on which page level locking is disabled.

 

——–FIND INDEXES ON WHICH PAGE LEVEL LOCKING IS DISABLED———–

SELECT OBJECT_NAME(i.object_id) as TableName
, name as IndexName
, allow_page_locks
FROM sys.indexes as i WHERE ALLOW_PAGE_LOCKS = 0

Above script will list out Index with table name on which page level locking is disabled, now you can enable then by using Query1, or by using Management studio link provided above.

But to make it easy I also build one another query, that will generate Alter Index statement for all index which have page level locking disabled.

————-SCRIPT TO GENERATE TO ENABLE PAGE LEVEL LOCKING IN INDEXES —————–

SELECT 'ALTER INDEX ' +i.Name+ ' ON ['
+ts.TABLE_CATALOG+ '].[' +ts.TABLE_SCHEMA+ '].[' +ts.TABLE_NAME+']'
+' SET (ALLOW_PAGE_LOCKS = ON)' as TSQL_Statement
FROM sys.indexes i LEFT OUTER JOIN sys.tables t
ON i.object_id = t.object_id
JOIN INFORMATION_SCHEMA.TABLES ts
ON ts.TABLE_NAME = t.name
WHERE i.allow_page_locks = 0
AND t.Name IS NOT NULL

Above query will generate alter index statement for all index on which page level locking is disabled.