Post reply

Index cannot be reorganized because page level locking is disabled error in SQL Server

  • March 4, 2016 at 7:22 am

    #309167

    "Index cannot be reorganized because page level locking is disabled error in SQL Server"

    Hi Guys,

    Reorganize index fails for one of the indexes. This(maintenance) was running fine for years and no changes were made to the index.

    Can you guys shed some light on why this is causing the failure to re-organize index and how this to be fixed.

    Many thanks!

  • March 4, 2016 at 7:44 am

    #1863117

    As the error says, the index had had page level locking disabled on it, via an ALTER INDEX statement.

    You can either enable page level locking or switch to rebuilding the index instead of reorganising it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 4, 2016 at 8:09 am

    #1863135

    Thanks Gail. Is there any negative impact if page locking is enabled? Should this be turned on for all indexes?

Viewing 3 posts - 1 through 3 (of 3 total)

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