Post reply

Page Level Locking Error

  • July 25, 2008 at 10:39 am

    #122681

    Hi,

    I am running following statement

    ALTER INDEX [idx_Users_Username] ON [dbo].[aspnet_Users] REORGANIZE WITH ( LOB_COMPACTION = ON )

    but i am getting the following error

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

    any idea??

    Thanks,

    Usman

  • July 25, 2008 at 10:59 am

    #848593

    http://msdn.microsoft.com/en-us/library/ms188783.aspx

    CREATE INDEX has a row and page level locking option. Someone created the index with it set to OFF.

  • July 25, 2008 at 11:05 am

    #848600

    Thanks,

    Whats the effect(benefi/drawback) of enabling this option?

  • July 25, 2008 at 11:17 am

    #848617

    By default, an index created using the Management Studio GUI will have page locks set to off. You can change the index properties by running:

    ALTER INDEX index_name ON table_name SET (ALLOW_PAGE_LOCKS = ON)

    SQL = Scarcely Qualifies as a Language

  • July 25, 2008 at 11:18 am

    #848618

    Per this article:

    http://msdn.microsoft.com/en-us/library/ms188388.aspx

    an index cannot be rebuilt with ALLOW_PAGE_LOCKS = OFF. I would venture to say that the rebuild happens one page at a time, locking the page that it is working with, so the database does not need to be offline. The ALTER INDEX command allows you to specify the option of ALLOW_PAGE_LOCKS = ON.

    I'm not sure whether this will change the option permanently on the specified index.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply