Query to find page locks disabled for all indexes

Generally, reorganize job fails due to page locks are not enabled,  even though if we enable for specific  index, there may be chance of page lock disabled for other indexes, so we need to make sure all page locks should be enabled if we run reorganize indexes.

So please find the below query to find the list of indexes that page locks are disabled.

SELECT OBJECT_SCHEMA_NAME(object_id) AS object_schema,

       OBJECT_NAME(object_id)        AS object_name,

       name                          AS index_name

FROM   sys.indexes

WHERE  OBJECT_SCHEMA_NAME(object_id)<>'sys' and allow_page_locks = 1

 

So going furthur make sure enable page locks for all indexes, if you are planning for weekly reorganize.