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