The index “[IndexName]” on table “[TableName]” cannot be reorganized because page level locking is disabled.

While looking at my laptop screen, I got new a email with subject "a maintenance job has been failed on a production server. Please look into it". When I opened that email the error description was like:

"The index “[IndexName]” on table “[TableName]” cannot be reorganized because page level locking is disabled."

The error description was more than enough to understand the actual issue. The error was due to the index reorganization job, while it was trying to de-fragment the index. But it was not able to do so because the index has the page level locking option disabled. So, the job failed.

By default, page level locking is enabled for indexes.As I did not face this kind of issue previously so I don't have idea how it got disabled. But will share soon. 

To fix the Job, I have enabled the page level locking option and the job ran successfully.

To find all the indexes in the database that have page level locking disabled, you can run the following query:


--List of Indexes that have page level locking disabled
SELECT OBJECT_NAME(i.object_id) as Table_Name
, name as IndexName
, allow_page_locks
FROM sys.indexes as i
WHERE ALLOW_PAGE_LOCKS = 0

Thanks!!