Thursday, February 16, 2012

Msg 2552 Level 16 State 2 Line 1

The index "PK_EmployeedID" (partition 1) on table "Employees" cannot be reorganized because page level locking is disabled. 

If you try to reorganize an index using a command like the following

USE [AdventureWorksDW];ALTER INDEX [PK_DimAccount] ON [dbo].[DimAccount] REORGANIZE  

And you get the above message then it means that you have disabled PAGE LEVEL LOCKING.
Page level locking when enabled helps in locking each page so that way the lock does not have to escalate from a row to a table.

But this will indeed prevent you from reorganizing the index since that is an online operation and you need to move pages.

There are two solutions for this.

1) Enable page level locking: But this might have serious performance repercussions. Until now I haven't seen any particular disadvantage in having page level locking enabled but be careful when you enable that.
2) Rebuild the index: If the first option is not possible then rebuild the index instead of reorganizing it.

No comments:

Post a Comment