Page level locking disable/enable during alter index reorganize

  • U J

    SSCrazy

    Points: 2420

    Alter index reorganize operation is failing with below error(as page level locking is disabled on few tables)

    "The index "[IndexName]" on table "[TableName]" cannot be reorganized because page level locking is disabled."

    Now to get rid of this issue,we can enable the page level locking "but" developer don't want to enable the page level locking due to some existing known concurrency issues so as a DBA, I have suggested to perform the maintenance in below order,

    1) Enable the page level locking on the tables for which its disabled

    2) Run the maintenance plan

    3) Disable the page level locking on the tables for which its enabled in step1

    Obviously the maintenance will be run during off business hours so other than blocking/resource consumption - will the above steps create any unforeseen issues ? For e.g. recompilation,generation of new execution plans or any other known adverse impact??

    Many thanks in advance.. 🙂

  • U J

    SSCrazy

    Points: 2420

    anyone to help?

  • x

    SSC-Insane

    Points: 23564

    I know the post is a bit old but I just wanted to add that I've successfully done this and have not seen any adverse issues on both 2008 and 2012. I do not use the maintenance plans however, I use a T-SQL job.

    edit: sorry, I'm too dumb to see what thread I'm in, I have not done this in 2005, however, I still can't think of any issues, especially if you're saying that you have a maintenance window. I actually do mine without a maintenance window.

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

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