The index “Index_name” (partition ) on table “Table_name” cannot be reorganized because page level locking is disabled

Ekrem Önsoy

5 years ago

ERROR MESAGGE:

“The index “Index_name” (partition ) on table “Table_name” cannot be reorganized because page level locking is disabled”

EXPLANATION:

If the Index in which you are performing the Reorganization process is “Disabled” or “ALLOW_PAGE_LOCKS” is set to “OFF”, you will see this error message and the Reorganization process will fail.

SOLUTION:

If your index is “Disabled”, you must set it to “Enabled”.

You can use the following code to enable Index:

ALTER INDEX Index_name ON Table_name REBUILD

1	ALTER INDEX Index_name ON Table_name REBUILD

Note: If the version you are using supports online index rebuild , I recommend that you run the script as follows.

ALTER INDEX Index_name ON Table_name REBUILD WITH(ONLINE=ON)

1	ALTER INDEX Index_name ON Table_name REBUILD WITH(ONLINE=ON)

If the “ALLOW_PAGE_LOCKS” option of your Index is set to “OFF”, then you must set it to “ON”.

You can use the following code to change the status of ALLOW_PAGE_LOCKS option:

ALTER INDEX Index_name ON Table_name SET (ALLOW_PAGE_LOCKS = ON)

1	ALTER INDEX Index_name ON Table_name SET (ALLOW_PAGE_LOCKS = ON)