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