Index maintenance, excluding index with page_level_lock disabled

  • Hi all,

    I'm running into an issue with not being able to rebuild / reorganize an index because allow_page_locks is not enabled on certain indexes. The database was provided by a 3rd party, so changing the setting on the indexes is not really an option.

    In the maintenance plan I changed the T-SQL code to exclude the tables where allow_page_locks is set to 0. When running the code against the database in a regular query, the indexes with the allow_page_locks set to 0 are not returned. When running the maintenance job, it fails on the same index that should be excluded. Below is my code, can anyone tell me what is going wrong here?

    [font="Times New Roman"]--SELECT

    -- object_id AS objectid,

    -- index_id AS indexid,

    -- partition_number AS partitionnum,

    -- avg_fragmentation_in_percent AS frag

    -- INTO #work_to_do

    -- FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL)

    -- WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

    --

    --new code below here

    SELECT

    a.object_id AS objectid,

    a.index_id AS indexid,

    a.partition_number AS partitionnum,

    a.avg_fragmentation_in_percent AS frag

    INTO #work_to_do

    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) a

    inner join sys.indexes b

    on a.object_id = b.object_id

    WHERE b.allow_page_locks > 0 AND a.avg_fragmentation_in_percent > 10.0 AND a.index_id > 0;

    --end new code[/font]

    Cheer, Rick

  • Can you go through this.

    http://www.sqlservercentral.com/Forums/Topic518826-360-1.aspx#bm518862

    Regards
    Durai Nagarajan

  • Durai,

    Thank you. As I cannot change the database on a permanent basis, as we do not own the design of it, I may have to do a workaround of allowing the page locks, and then disallowing them after index maintenance has been completed.

    What I do not understand yet, is why my script is still trying to modify the index on tables with allow_page_locks set to off (0), while I exclude them from the selection.

    Cheers,

    Rick

  • allow_page_locks is a bit field try =1 and proceed

    Regards
    Durai Nagarajan

  • Changed the code to =1, but I still get the same error during the maintenance...

    [font="System"]

    The index "PK_TABLE" (partition 1) on table "DB_TABLE" cannot be reorganized because page level locking is disabled.[/font]

    So it is still adding that index to the #work_to_do table even though running the select statement from my query windows does NOT return that table based on the setting allow_page_locks=1 .

    What am I missing?

    Cheers,

    Rick

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply