Page Level Locking

ryanatworkryanatwork Member Posts: 47
edited 2010-08-11 in SQL Performance
Hi,

Getting this error when trying to rebuild an index to a table.

Executing the query "ALTER INDEX [IDX_Billto_SalesDate] ON [dbo].[Company..." failed with the following error: "The index "IDX_Billto_SalesDate" (partition 1) on table "Company$Sales Shipment Line" cannot be reorganized because page level locking is disabled.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I have recently done a technical upgrade by opening the database in 2009SP1.

I know that I could alter the index to allow it but don't know if that is a good idea.

Cheers,
R

Comments

  • DenSterDenSter Member Posts: 8,304
    That index was added directly in SSMS, it is not part of NAV. NAV indexes are called '$1', '$2', and primary keys are called '<companyname>$<tablename>$0'. As far as NAV is concerned you can just remove the whole thing. I'd find out how it got there first though.
  • strykstryk Member Posts: 645
    I know that I could alter the index to allow it but don't know if that is a good idea.
    I would sugest this, too!
    USE [Database]
    GO
    ALTER INDEX [IDX_Billto_SalesDate] ON [dbo].[Company$Sales Shipment Line] 
    SET ( ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, STATISTICS_NORECOMPUTE  = OFF )
    

    I guess the index was created/modified with the options "ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF" which is now causing the trouble.
    Does this help?
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • ryanatworkryanatwork Member Posts: 47
    Thanks for the valuable advice.

    Deleted.

    Cheers,
    R
Sign In or Register to comment.