When you install SharePoint 2010 you might discover that you can't use the SQL maintenance to clean up your indexes. You will get message like "failed to reorganize indexes due to page level locking is disabled"
I spent some time look into this issue and developed a store procedure that will fix this issue.
How to fix SharePoint 2010 failed to reorganize indexes due to page level locking is disabled?
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