Unable to Reorganize Index – ALLOW_PAGE_LOCKS Disabled

Unable to Reorganize Index – ALLOW_PAGE_LOCKS Disabled

May 18, 2016

In a certain database I work with, ALLOW_PAGE_LOCKS and ALLOW_ROW_LOCKS has been disabled for a number of indexes. This setting caused my default index rebuild job to fail at the REORGANIZE step. The error returned was:

Msg 2552, Level 16, State 1, Line 1 The index PK_Pages (partition 1) on table core_Pages cannot be reorganized because page level locking is disabled

To resolve this problem I first of all identified the indexes affected using the script below:

select * from sys.indexes where allow_row_locks = 0 or allow_page_locks = 0

I went further to generate a script that would enable the options for the affected indexes and another that would disable them after the rebuild steps:

select ‘ALTER INDEX [‘ + name + ‘] ON [dbo].[‘ + object_name(parent_object_id) + ‘] SET ( ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON )’
from sys.objects
where name in (select name from sys.indexes where allow_row_locks = 0 or allow_page_locks = 0)

I then inserted the resulting script as the first step in my index rebuild job. I also included an additional step that would disable page and row locks back. I took this root because I could not immediately get feedback from the developers on why the options were disabled in the first place. Beside, it was obviously deliberate since these options are enabled by default.

JobIndex

 

Igiri Books © Copyright 2016. All Rights Reserved. Site Credits: AppWorld