Post reply

index rebuild removes locking properties

  • November 11, 2002 at 7:46 am

    #58208

    I have tried to disallowed row and page locks on some tables to prevent deadlocks.

    That works fine until the index is rebuilt by a maintenance plan. Then these properties are just gone.

    This can easily demonstrated with the following in the pubs database:

    sp_indexoption 'authors', 'AllowRowLocks','FALSE'

    select indexproperty(object_id('authors'),'aunmind','IsRowLockDisAllowed')

    -- > gives 1

    DBCC dbreindex ('authors')

    select indexproperty(object_id('authors'),'aunmind','IsRowLockDisAllowed')

    -- > gives 0

    Apparently these properties are not remembered.

    I solved this by adding the disallow statements in step 2 of the job.

    I really would like to have some customers of us with the deadlock problem to work only with full index locks. These companies have no DBA's and meddling with jobs is really too complicated for them.

    Could anybody tell me if there is a way to reindex while keeping the RowLockDisAllowed and PageLockDisAllowed properties?

    Joachim.

  • November 11, 2002 at 12:41 pm

    #440993

    Most everything done is rememebered and this could be a bug. I would first try the latest Service Pack for your SQL version to see if goes away. If not then check with MS as I find no related info on this. Also, what version of SQL Server are you running this on.

  • November 12, 2002 at 4:36 am

    #441098

    This is SQLServer 2000 SP2. I still have to check it out for SQLServer 7.

  • November 12, 2002 at 5:02 am

    #441100

    I check SQL 7 SP4 and did not remove. I will see if I can find additional information.

  • November 12, 2002 at 5:33 am

    #441102

    I also just tested SQLserver 7 sp 4 without any problems.

    I guess it is a bug.

  • November 12, 2002 at 5:57 am

    #441104

    I believe so. I will check SQL 2000 and SP1 to see if this has always existed. Will also throw into testing with SP3 with a friend just to see what happens.

  • November 13, 2002 at 4:06 am

    #441213

    I am just upgrading SQLServer 7 to SQLServer 2000.

    A test shows that SQLServer 2000 without Service Pack also has the problem.

  • November 13, 2002 at 5:59 am

    #441224

    As I was busy anyway, I tried it with SP1.

    The problem also occurs here.

  • November 13, 2002 at 7:03 am

    #441230

    Thank for the update, now I don't have to test those. Overall it appears is a 2K problem, do not know yet about SP3.

Viewing 9 posts - 1 through 8 (of 8 total)

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