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

Submitted by chanh on

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

Forums: