KB Article # 7744
Topic/Category: database, activiti
Issue: The index “IX_BATCH_CLASS_PLUGIN_CONFIG_BATCH” on table “batch_class_plugin_config” cannot be reorganized because page level locking is disabled.
By default, we have both ROW and PAGE locks enabled in SQL server. SQL Server chooses ROW lock granularity for most cases but may choose PAGE lock where appropriate.
An Index Reorganize operation is dependent on Page Level locks. Therefore a simple Reorganize of the index having Page Lock disabled will fail :
The index “IX_BATCH_CLASS_PLUGIN_CONFIG_BATCH” on table “batch_class_plugin_config” cannot be reorganized because page level locking is disabled.
REORGANIZE requires page locks to remove fragmentation (pages not in order), pages must be locked and moved, which is not possible if page locks have been disabled. So, the page level locks for the indexes should be enabled to allow Reorganize of indexes. If deadlocks are still observed, we should recommend them to remove the page locks manually as mentioned in the wiki. For convenience, attached are the pagelevellocks scripts. In this case when page locks are disabled, the only way to remove fragmentation is by REBUILD the indexes. REBUILD indexes requires only 1 connection to the database. Thus, recommended solution is the first one i.e., working with REORGANIZE. However, if deadlocks are observed, page locks can be removed as suggested above.