Index Reorganization Error in SQL Server 2005

If you ever see an error like this:

Msg 2552, Level 16, State 2, Line 1
The index "PK_FeedRecommendations" (partition 1) on table "FeedRecommendations" cannot be reorganized because page level locking is disabled.

when you try to run an ALTER INDEX REORGANIZE, it means that the index was created without the "Use page locks when accessing the index" option turned on, (see below).  Unfortunately, the default when you use SQL Server Management Studio (SSMS) to create an index is for that option to be turned off.

 

 

From the SQL Server 2005 BOL:

ALTER INDEX REORGANIZE

Specifies the index leaf level will be reorganized. This clause is equivalent to the older DBCC INDEXDEFRAG. ALTER INDEX REORGANIZE statement is always performed online. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction. REORGANIZE cannot be specified for a disabled index or an index with ALLOW_PAGE_LOCKS set to OFF.

Here is the exact syntax for this command:

ALTER INDEX PK_PostLinks ON dbo.FeedRecommendations
REORGANIZE ;

This entry was posted in SQL Server 2005. 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