Index Page level Locking is Disable ERROR

Introduction

When we are working with production database, sometime 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 fails. In this article we are trying to explain it and solve it.

Why this Errors Occurs

By default the page level locks should be enabled for Index. The main causes of this error are the Index Reorganize step has hits an Index that have page lock disables. This somehow your Index Page Lock is disabled.

How to Solve it

Finding the Table Name and Index Name where Page Lock is Disable

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;

Now Use Alter Statement to Allow Page Lock On

The syntax is mentioned bellow

ALTER INDEX <Index_Name> ON <Table_Name>

SET (ALLOW_PAGE_LOCKS = ON);

To generate the Alter Script Automatically

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;

Hope you like it.

Posted by: MR. JOYDEEP DAS