SQL Server 2005 - Indexes Reorganization

When you create a Maintenace Plan for SQL Server 2005 with a "Reorganize Index Task" very often you'll get the following error:

Executing the query "ALTER INDEX [IX_myIndex] ON [dbo].[myTable] REORGANIZE WITH ( LOB_COMPACTION = ON )
" failed with the following error: "The index "IX_myIndex" (partition 1) on table "myTable" cannot be reorganized because page level locking is disabled.

This is due to the default behavoir when you create an index with the GUI in SQL Server Management Studio which is to disable page-level locking for the index. The behavoir is changed with SQL Server 2005 SP2 (obviously it must be applied to the clients).

When you try to reorganize a database you may find a lot of indexes with page level locking disabled.

To enable it, you can execute the following script on every impacted database (I've got this script from http://www.megasolutions.net/Sqlserver/Finding-all-the-indexes-on-a-server-with-AllowPageLocks-set-to-FALSE_-65676.aspx)

The script will output the sql to update the indexes, so copy the sql and execute it again.

set quoted_identifier off
go
SELECT "alter index [" + i.name + "] ON [" + s.name + "].[" + t.name + "]
SET (ALLOW_PAGE_LOCKS = ON) "
--    s.name, t.name, i.name
FROM sys.schemas s
JOIN sys.tables t ON
    t.schema_id  = s.schema_id
JOIN sys.indexes i ON
    i.object_id = t.object_id
WHERE
    i.index_id > 0
    AND INDEXPROPERTY(i.object_id, i.name, 'IsPageLockDisallowed') = 1
    AND INDEXPROPERTY(t.object_id, i.name, 'IsStatistics') = 0
    AND NOT EXISTS
    (
        SELECT *
        FROM sys.objects keys
        WHERE
            keys.parent_object_id = i.object_id AND
            keys.name = i.name AND
            keys.type IN('PK', 'UQ')
    )