Msg: 2522 The index "<name>" (partition 1) on table "TableName" cannot be reorganized because page level locking is disabled.
All of sudden the re-org of an index started failing and no error was displayed within that scheduled job we execute. After executing the code from Query Editior I was able to get the message as follows:
Msg 2552, Level 16, State 1, Line 3
The index "<name>" (partition 1) on table "TableName" cannot be reorganized because page level locking is disabled.
Using ALTER INDEX REORGANIZE statement as reinstated in the BOL ...the index leaf level will be reorganized. This clause is equivalent to the older DBCC INDEXDEFRAG. ALTER INDEX REORGANIZE statement is always performed online. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction. REORGANIZE cannot be specified for a disabled index or an index with ALLOW_PAGE_LOCKS set to OFF.
So by default under SSMS when you try to create an index is for that option to be turned off. You have to specify ALLOW_PAGE_LOCKS to ON and index cannot be reorganised as per the error above. Further the database might go under suspect if the allow_page_locks option is disabled for the table that does not have a clustered index, which was fixed within latest Service Pack for SQL 2005.
Also you can run the following statement against a database to find out whether any tables in the database may have this problem: select * from sys.indexes where index_id = 0 and allow_page_locks = 0