1

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

  1. Why would page level locking be disabled on an Index?
  2. If I go ahead and make changes and allow page level locking, it is not going to break anything, right?
  3. Is there a script to make all the indexes allow page level locking?
1

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
  • @Max Vernon: Thanks for the script but actually I was looking for a script which can mass change that option in all the indexes of the database. – Jack Sep 28 '17 at 18:34
  • Here is one - connectsql.com/2012/06/… – rvsc48 Sep 28 '17 at 18:39
  • @Jack - I only edited the answer provided above. – Max Vernon Sep 28 '17 at 18:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.