While trying to figure out why a Reorganise Indexes task on SQL Server 2005
kept failing, I've discovered that any indexes created via SMSS have
AllowPageLocks set to false (due to SMSS leaving "Use page locks when
accessing the index" unticked by default when creating an index). This
causes the Reorganise task to fail with the error

Executing the query "ALTER INDEX [<index name>] ON [<table name>] REORGANIZE
WITH ( LOB_COMPACTION = ON )
" failed with the following error: "The index "<index name>" (partition 1)
on table "<table name>" cannot be reorganized because page level locking is
disabled.". Possible failure reasons: Problems with the query, "ResultSet"
property not set correctly, parameters not set correctly, or connection not
established correctly.

So now I need to find every index on my server that has been created without
AllowPageLocks enabled, and enable it. Is there an easy way to pull up the
AllowPageLocks option value for all indexes across a server in one go? I
could then use this to generate a list of sp_indexoption statements to
enable page locks on those indexes that were created with it turned off.

Also, is there a way to change SMSS such that the default setting for "Use
page locks when accessing the index" is ticked? Seems strange to have this
defaulted to off.

Dan

SELECT
    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') = 0
    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')
    )

--
Hope this helps.

Dan Guzman
SQL Server MVP