When we are working with a production database, sometimes we find a specific message in maintenance plan log.
The index [IndexName] on table [TableName]
cannot be reorganized because page level locking is disabled.
This will cause our Index Reorganize steps to fail. In this article, we are trying to explain it and solve it.
Why this Error Occurs
By default, the page level locks should be enabled for Index. The main cause of this error is that the Index Reorganize step has hit an Index that has page lock disabled. This somehow your Index Page Lock is disabled.
How to Solve it
Finding the Table Name and Index Name where Page Lock is Disabled
SELECT T.Name AS [Table Name], I.Name As [Index Name] FROM sys.indexes I LEFT OUTER JOIN sys.tables T ON I.object_id = t.object_id WHERE I.allow_page_locks = 0 AND T.Name IS NOT NULL;
ALTER INDEX <Index_Name> ON <Table_Name> SET (ALLOW_PAGE_LOCKS = ON);
SELECT 'ALTER INDEX ' + I.Name + ' ON ' + T.Name + ' SET (ALLOW_PAGE_LOCKS = ON)' As Command FROM sys.indexes I LEFT OUTER JOIN sys.tables T ON I.object_id = t.object_id WHERE I.allow_page_locks = 0 AND T.Name IS NOT NULL;