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
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
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
FROM sys.objects keys
keys.parent_object_id = i.object_id AND
keys.name = i.name AND
keys.type IN('PK', 'UQ')