SQL Server Reorganize Index Job I have an index reorganize job on SQL Server 2012 that fails because page level locking is disabled on it.  My three questions are:

Why would page level locking be disabled on an Index?

 If I go ahead and make changes and allow page level locking, it is not going to break anything, right?

Is there a script to make all the indexes allow page level locking?
Why would page level locking be disabled on an Index?  


By default, [allow_page_locks] is set to 1, so, someone must have changed it in the past to avoid that level of locking.  


  If I go ahead and make changes and allow page level locking, it is not going to break anything, right?  


I would test this in a non-prod environment prior to changing.  I would think the outcome would be unexpected locking. 


  Is there a script?  


You could use this script to identify those indexes and then change it to modify those.  But, I would think there was probably a good reason for the change in the first place.

Select Indx.name,
    Indx.type_desc,
    Indx.is_disabled,
    Indx.allow_page_locks,
    Indx.allow_row_locks 
from sys.indexes Indx
left outer join sys.objects OBJ 
    on OBJ.object_id=Indx.Object_id
where allow_page_locks = 0