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

  • Hi,

    Actually i work in Teradata. Dono much abt SQl server.

    I have a query which is throwing the error

    The index "xfer_id" (partition 1) on table "mdw_xfer_s2" cannot be reorganized because page level locking is disabled.

    the query is:

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',

    ' + RTRIM(@indexid) + ') - fragmentation currently '

    + RTRIM(CONVERT(varchar(15),@frag)) + '%'

    SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',

    ' + RTRIM(@indexid) + ')'

    EXEC (@execstr)

    FETCH NEXT

    FROM indexes

    INTO @tablename, @objectid, @indexid, @frag

    END

    while executing the second step it is throwing the above error.

    Can you please tell how can i use the solution u provided in this?

    thanks so much in advance. Im waiting for the reply badly.

  • guna.vaishu (7/1/2011)


    The index "xfer_id" (partition 1) on table "mdw_xfer_s2" [font="Arial Black"]cannot be reorganized because page level locking is disabled.[/font]

    The error pretty much says it all. In order for the index to be reorganize, you going to have to, at least temporarily, all page level locking on the index.

    --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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • You *could* drop & re-create the index (which will implicitly re-organise it)...but that's "using a sledgehammer" and you'll possibly get a few unwanted side-effects e.g. lock everyone out of the table.

    I'd also hazard a guess that the required disk space may be far more than for re-organising the index.

    HTH

    Andy

  • sisiralek (9/8/2008)


    :)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:)

    This is great, thank you!

  • I guess this link will be helpful 😀

    http://connectsql.blogspot.in/2012/06/sql-server-script-to-fix-allowpagelocks.html 😎

    Thanx,

    Ronald

  • Please note: 7 year old thread

    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
  • True, but the link is still helpful to others that might find this thread.

    --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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 16 through 22 (of 22 total)

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