How to fix SharePoint 2010 failed to reorganize indexes due to page level locking is disabled?

Pick Language to Auto Translate:
AR | BG | CA | CS | DA | DE | EL | ES | FI | FR | HI | HR | ID | IT | IW | JA | KO | LT | LV | NL | NO | PL | PT | RO | RU | SK | SR | SL | SV | TL | UK | VI | ZH | ZH-TW

The problem can easily be fixed with this little store procedure below.

Just create and run spFixAllowPageLocks listed below in the database which you want to fix the issue.

-- Store Procedure to fix all tables with indexes that has allow_page_locks is OFF
create PROCEDURE [dbo].[spFixAllowPageLocks]
AS
BEGIN
Declare @TableName nvarchar(max)
Declare @IndexName nvarchar(max)
declare @sql nvarchar(max)=''

-- get all record that has allow_page_locks = off
Declare c cursor for
SELECT OBJECT_NAME(object_id) AS [Table_Name], name as Index_Name FROM sys.indexes si where allow_page_locks = 0 AND NOT EXISTS ( SELECT object_id FROM sys.internal_tables it WHERE it.object_id = si.object_id)

-- loop through the list to fix one by one
open c
fetch c into @TableName, @IndexName
while @@fetch_status=0
begin
select @sql = 'ALTER INDEX '+@IndexName+' ON '+@TableName+' set (ALLOW_PAGE_LOCKS = ON)'
exec(@sql)
print 'Table ['+ @TableName + '] with index [' + @IndexName + '] has been fixed!'
fetch next from c into @TableName, @IndexName
end
close c
deallocate c
if len(@sql) = 0
begin
print 'Nothing to do!'
end
SET NOCOUNT ON;
END