"..table "ItemNum" cannot be reorganized because page level locking is disabled"

  • Barkingdog

    SSCoach

    Points: 18670

    I get this error message when I attempt to backup a sql 2005 database. The databse is set to Simple recovery mode and I used the SSMS Database Maintenance Wizard to create the job. Any idea how to "enable page level locking?"

    TIA,

     

    Bill

     

     

    Executing the query "ALTER INDEX [idx_ItemNum] ON [dbo].[ItemNum]

    REORGANIZE WITH ( LOB_COMPACTION = ON )

    " failed with the following error: "The index "idx_ItemNum" (partition 1) on

    table "ItemNum" 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.

  • Patrick SCHNEIDER

    SSC Journeyman

    Points: 95

    Page level locking can be set using the CREATE INDEX or ALTER INDEX statements :

    ALTER INDEX index_name ON table_name SET (ALLOW_PAGE_LOCKS = ON)

    The allow_page_locks column of sys.indexes view may help you find indexes having page level locking disabled.

    Hope this helps.

    Patrick

  • Maurice Pelchat

    Mr or Mrs. 500

    Points: 537

    The problem is that I have also this porblem that appears spuriously from differents index on my development box.

    I suspect a bug in the SQL Server management Studio, which turns the option on, when some other modifications are done to an index.  This option is available from SSMS but hard to find, and the programmer who worked with the index was not even aware of it existance.

    So even when watching carefully what happens we don't see where the option gets turned on.

    I'm wondering if i could activate some DDL trigger to get prompted when this happen.  Could be a solution.

    I use SP2 latest with patches.

  • Maurice Pelchat

    Mr or Mrs. 500

    Points: 537

    Found why....

    By checking more how index are created by my developers, if found that one method for index creation is setting page level locking to on by default and the other is setting it to off.

    If you set right click on index folder under a specific table, and you select new index, the option is set to off by default in index creation dialog.  You have to go to the options page to turn it on.

    If you go through the table context menu and select modify, to add an index, then the option is ON by default for the new index created.

    I suspect that a lot of people are going to wonder why reorganize fail spuriously on some index on development boxes.  Here you have an hint.

  • Barkingdog

    SSCoach

    Points: 18670

    Interesting ideas.

    What is the "recommended" approach -- enabling page level locking for index reoroganizaiton or not?

    Barkingdog

  • noeld

    SSC Guru

    Points: 96590

    Page level Locking is "ON" by default.

    You should disable it only on *very rare* cases.

    Nice SSMS BUG catch by the way !!!!


    * Noel

  • Jeff Moden

    SSC Guru

    Points: 993661

    Is it a bug or did the boys in Redmond just decide to change a default like they did with the CONCATENATE NULL YIELDS NULL setting?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • shashi kant

    SSCommitted

    Points: 1659

    Thanks Patrick Schneider

    Nice solution for this problem and your Provide solution

    ALTER INDEX index_name ON table_name SET (ALLOW_PAGE_LOCKS = ON)

    is working fine in my case.

    Thanks & Regards

    Shashi Kant chauhan

  • sisiralek

    SSC Enthusiast

    Points: 180

    :)This is a good solution

    SET NOCOUNT ON

    DECLARE @DBName nvarchar(50), @INName nvarchar(50)

    DECLARE @ODBName nvarchar(50), @OINName nvarchar(50)

    Declare @execstr nvarchar(200)

    --PRINT '-------- Vendor Products Report --------'

    DECLARE Index_cursor CURSOR FOR

    Select A.Name as InName,ob.Name as DBName from sys.indexes A

    left outer join sys.objects ob on ob.object_id=A.Object_id

    where allow_page_locks=0 and ob.type='U'

    -- Select only allow_page_locks 0 and User Tables

    OPEN Index_cursor

    FETCH NEXT FROM Index_cursor

    INTO @INName, @DBName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT @DBName +' ' + @INName

    --PRINT @INName

    SET @ODBName = ltrim(rtrim(@DBName))

    SET @OINName = ltrim(rtrim(@INName))

    SELECT @execstr = 'ALTER INDEX '+@OINName+ ' ON '+

    @ODBName+' SET (ALLOW_PAGE_LOCKS = ON)';

    EXEC (@execstr);

    FETCH NEXT FROM Index_cursor

    INTO @INName, @DBName

    END

    CLOSE Index_cursor

    DEALLOCATE Index_cursor

    Cheers,

    Ranjith:)

    Ranjith Lekamalage
    MCITP SQL 2012 (BI Development)

  • Brian.Dunat

    SSC Eights!

    Points: 824

    Thanks Ranjith. This was a great "quick fix" and now all our weekly database maintenance jobs are running sucessfully.

  • simon spring

    SSC Journeyman

    Points: 92

    Exactly what i was after, many thanks!

  • Eric Geil

    SSC Enthusiast

    Points: 199

    Very nice! I was having the same issue with my "Reorganize Index Task". I added the above solution right to my maintenance plan, immediately before the Reorganize Index Task and it worked perfectly. It should also prevent any such issues from popping up down the road. Thanks.

    E

  • timexist

    SSC Veteran

    Points: 238

    Thanks for the quick fix,

    I turned on locks

    SET (ALLOW_PAGE_LOCKS = ON),

    ran my job,

    then switched it off again

    SET (ALLOW_PAGE_LOCKS = OFF).

    have fun

    Ben:-D

  • N Cook

    SSC Eights!

    Points: 881

    SET NOCOUNT ON

    DECLARE @DBName nvarchar(50), @INName nvarchar(50)

    DECLARE @ODBName nvarchar(50), @OINName nvarchar(50)

    Declare @execstr nvarchar(200)

    --PRINT '-------- Vendor Products Report --------'

    DECLARE Index_cursor CURSOR FOR

    Select A.Name as InName,ob.Name as DBName from sys.indexes A

    left outer join sys.objects ob on ob.object_id=A.Object_id

    where allow_page_locks=0 and ob.type='U'

    -- Select only allow_page_locks 0 and User Tables

    OPEN Index_cursor

    FETCH NEXT FROM Index_cursor

    INTO @INName, @DBName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT @DBName +' ' + @INName

    --PRINT @INName

    SET @ODBName = ltrim(rtrim(@DBName))

    SET @OINName = ltrim(rtrim(@INName))

    SELECT @execstr = 'ALTER INDEX '+@OINName+ ' ON '+

    @ODBName+' SET (ALLOW_PAGE_LOCKS = ON)';

    EXEC (@execstr);

    FETCH NEXT FROM Index_cursor

    INTO @INName, @DBName

    END

    CLOSE Index_cursor

    DEALLOCATE Index_cursor

    Thanks, this was a timesaver.

    Edit: I didn't see the second page of the thread and therefore didn't realize so many people had already thanked him for this. Sorry to revive an old thread.

  • jon-230788

    SSC Journeyman

    Points: 82

    Odd. I have a indexes that actually looking at how they are defined are already set to ALLOW_PAGE_LOCKS = ON, but I still receive this error.

    Altering the index setting prior to attempting to reorganize allows me to work around, but the index is already set to allow page locks.

    Any answers to that one? Or is this another bug in 2005 SP3?

Viewing 15 posts - 1 through 15 (of 22 total)

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