3/22/2008

PAGE LOCKS on INDEX


I have one of the maintenance job failed this morning. The failed step is:

ALTER INDEX ALL ON [table_name] REORGANIZE


The job was failed with the following error message:

The index "CX_ztbl_dm_os_memory_clerks_CreatedDate" (partition 1) on table "ztbl_dm_os_memory_clerks" cannot be reorganized because page level locking is disabled. [SQLSTATE 42000] (Error 2552). The step failed.

Reasons:
Be default when creating index, the option "Use page locks when accessing the index" option is checked. But for some reason this index was created without this option.

Solution:

Alter index [index_name] on table [table_name]
set (ALLOW_PAGE_LOCKS=ON)

Later I will provide the detail reason ... It should be start with how index created ...




No comments yet