Wednesday, December 11, 2013

Fix: Reorganize Index Task fails in Maintenance Plan

If you are using the Maintenance Plan creator in SSMS, you may have observed that your Reorganize Index tasks are failing. The error is difficult to read unless you log the result to a file.

This is due to one or more indexes not allowing PAGE LOCKs which are required for executing ALTER INDEX REORGANIZE. ALLOW_PAGE_LOCKS = ON has to be set for each index you wish to reorganize.

I found this code on SQL Server Central which will ALTER all indexes to allow PAGE LOCKs. Thank you Ranjith:

Use this query to determine if you have any indexes that disallow page locks:

SELECT A.Name AS InName,ob.Name AS DBName
FROM sys.indexes A
LEFT OUTER JOIN sys.objects ob ON ob.object_id=A.Object_id
WHERE ALLOW_PAGE_LOCKS=0 and ob.type='U'


And here is the full code block to allow page locks:

SET NOCOUNT ON
DECLARE @DBName nvarchar(50), @INName nvarchar(50)
DECLARE @ODBName nvarchar(50), @OINName nvarchar(50)
Declare @execstr nvarchar(200)
DECLARE Index_cursor CURSOR FOR
SELECT A.Name AS InName,ob.Name AS DBName
FROM sys.indexes A
LEFT OUTER JOIN sys.objects ob ON ob.object_id=A.Object_id
WHERE ALLOW_PAGE_LOCKS=0 and ob.type='U'
-- Select only allow_page_locks 0 and User Tables
OPEN Index_cursor
FETCH NEXT FROM Index_cursor
INTO @INName, @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @DBName +' ' + @INName
SET @ODBName = ltrim(rtrim(@DBName))
SET @OINName = ltrim(rtrim(@INName))
SELECT @execstr = 'ALTER INDEX '+@OINName+ ' ON '+
@ODBName+' SET (ALLOW_PAGE_LOCKS = ON)';
EXEC (@execstr);
FETCH NEXT FROM Index_cursor
INTO @INName, @DBName
END
CLOSE Index_cursor
DEALLOCATE Index_cursor