Day 17: Error – cannot be reorganized because page level locking is disabled

Description:

 

When we try to ALTER INDEX .. REORGANIZE we get below error for indexes which has ALLOW PAGE LEVEL LOCK = OFF.

 

cannot be reorganized because page level locking is disabled.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

 

 

Failed:(-1073548784) Executing the query “ALTER INDEX [xxx] ON [dbo].[xx] REORGANIZE WITH ( LOB_COMPACTION = ON )

” failed with the following error: “The index “xxxx” (partition 1) on table “xxxx” cannot be reorganized because page level locking is disabled.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

 

Index re-organize is the online process of re-organizing the leaf level pages, which requires page level locking should be ON.

http://dba.stackexchange.com/questions/6512/difference-between-row-level-and-page-level-locking-and-consequences

but for some reason, if developer creates an index using GUI –Sql Server Management studio (SSMS) and created an index and make the changes by –default this page level locking is OFF and can cause above error.

http://www.sqlservercentral.com/Forums/Topic301624-146-1.aspx

so we can create a custom query to enable all page-level lock to ON

http://support.k2techno.com.au/?W794

to overcome this problem.

 

Advertisements
This entry was posted in Troubleshooting and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s