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 )’
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.