none
page level locking

    Question

  •  

    I am running one maintenance plan which includes just "Chech DB Integrity" and "Reorganised Index". But it failed and I am getting following error:-

     

     

    FailedSad-1073548784) Executing the query "ALTER INDEX [CgiExclusion_ProfileId] ON [dbo].[CgiExclusion] REORGANIZE WITH ( LOB_COMPACTION = ON )
    " failed with the following error: "The index "CgiExclusion_ProfileId" (partition 1) on table "CgiExclusion" 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.

     

    Is there any idea what is going on and how to solve this problem?

    Friday, May 9, 2008 2:31 AM

Answers

  • Yes the issue gets resolved by enabling the page lock for the index specified in the alter statement.

    ALTER INDEX < Index name >  ON   < Table Name >
    SET (
        ALLOW_PAGE_LOCKS = ON
    )
    GO

     

    It disables some lock escalation. With page locks disabled, SQL can only lock at the row or table level for that index

     

    Thanks

    Sreekar

    Tuesday, December 9, 2008 1:19 PM
  • There's probably a better way but the script below ultimately accomplished what I needed it to do - per the question I posted immediately above.  The summary of what it does is that it queries for all user databases on the system, which it uses to populate a cursor containing database names.  A loop is used to query the [database name].sys.indexes table for each database where allow_page_locks is 0, while not returning indexes (queue_clustered_index and queue_secondary_index) that seem to be common to all databases but that don't cause the DB optimization plan to fail.  The TSQL is below:

    DECLARE @DBName NVARCHAR(50)
    DECLARE @DynamicSQL NVARCHAR(300)

    DECLARE @DBCursor CURSOR
    SET @DBCursor = CURSOR FOR
     SELECT NAME FROM SYS.DATABASES
     WHERE NAME NOT IN ('master','tempdb','model','msdb')

    OPEN @DBCursor
    FETCH NEXT FROM @DBCursor INTO @DBName

    WHILE @@FETCH_STATUS = 0
     BEGIN
      SET @DynamicSQL = 'SELECT * FROM [' + @DBName + ']' + '.sys.indexes WHERE allow_page_locks = 0 AND name <> ''queue_clustered_index'' AND name <> ''queue_secondary_index'''
      PRINT @DynamicSQL
      EXEC SP_EXECUTESQL @DynamicSQL

      FETCH NEXT FROM @DBCursor INTO @DBName
     END

    CLOSE @DBCursor
    DEALLOCATE @DBCursor  

    Wednesday, August 26, 2009 5:04 PM

All replies

  • Could you please Uncheck Compact Large Object check box in Reorganize Task and run again ?

     

    Madhu

     

    Friday, May 9, 2008 4:45 AM
    Moderator

  • Where to find th"Compact Large Object" check box? Where to find "Reorganize Task"?
    Friday, May 9, 2008 4:51 AM

  • Ok, i found where the option is. But now when I am trying to unchek "Compact lage object", I am unable to do so. I am getting following error. Any idea, now what to do?

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Reorganize failed for Index 'ix'.  (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Reorganize+Index&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    The index "ix" (partition 1) on table "ErrorLog" cannot be reorganized because page level locking is disabled. (Microsoft SQL Server, Error: 2552)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1406&EvtSrc=MSSQLServer&EvtID=2552&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Friday, May 9, 2008 5:09 AM
  •  saumen wrote:

    Where to find th"Compact Large Object" check box? Where to find "Reorganize Task"?

     

    Management Studio -->> Management -->> Maintenenace Plan -->> Open your MP -->> Right click and edit Index Reorganize task -->> you can find a check box there

     

    Madhu

    Friday, May 9, 2008 5:22 AM
    Moderator
  •  

    REORGANIZE cannot be specified for a disabled index or an index with ALLOW_PAGE_LOCKS set to OFF.

     

    Under SSMS when you try to create an index, that option is off by default. You have to specify ALLOW_PAGE_LOCKS  to ON, or index cannot be reorganised. That might be the reason that why you see error messages above.  Further the database might suspect  if the ALLOW_PAGE_LOCKS option is disabled for the table that does not have a clustered index, which was fixed within latest Service Pack for SQL 2005.

     

    You can run the following statement against a database to find out whether any tables in the database may have this problem:

     

    select * from sys.indexes where index_id = 0 and allow_page_locks = 0

    Friday, May 9, 2008 6:30 AM
  • Sorry for picking this topic up a bit late, but this just started occuring for me. Some fun facts:

     

    1. These are not newly created indexes (probably 1 year old).
    2. I work in a very tight environment, where very few people would have the ability to change an existing index or drop/ recreate an existing index. I don't know how/ why these indexes would have had the allow_page_lock setting changed.
    3. I re-index everything weekly.
    4. I have the "compact large objects" checked. However, this issue is occuring on tables with Clustered Indexes.
    5. This issue just started for me. Based on all these circumstances, this simply doesn't make sense.

    How could this have suddenly started?

     

     

     

     

    • Proposed as answer by Pete E Q Friday, January 16, 2009 6:26 PM
    Friday, December 5, 2008 4:28 PM
  • Oddly enough Chris, I'm getting the same error on a system here that the maint plan has been working on...  I ran the sql script ( select * from sys.indexes where index_id = 0 and allow_page_locks = 0) and recieve nothing.
    Monday, December 8, 2008 7:45 PM
  • Use this query to list the indexes with page file locking disabled:

    SELECT * FROM sys.indexes WHERE allow_page_locks = 0

     

    As for the other stuff I've seen people elsewhere mention that they would add steps to enable then disable page file locking when doing a REORGANIZE:

    ALTER INDEX < INDEX_NAME > ON < TABLE_NAME> SET (ALLOW_PAGE_LOCKS = ON)

    Monday, December 8, 2008 10:04 PM
  • Yes the issue gets resolved by enabling the page lock for the index specified in the alter statement.

    ALTER INDEX < Index name >  ON   < Table Name >
    SET (
        ALLOW_PAGE_LOCKS = ON
    )
    GO

     

    It disables some lock escalation. With page locks disabled, SQL can only lock at the row or table level for that index

     

    Thanks

    Sreekar

    Tuesday, December 9, 2008 1:19 PM
  • We are using SQL Server 2008.
     
    I had the same problem.  We had a back up maintenance plan that started failing.  The plan itself gave no real reason.  The sub plan had many steps in it.  So, I had to look into the history.  I noticed the error happened during the re-organizing of the indexes.  I created a script to replicate it.  When I ran that script, I figured out what database, table and index was causing the error.  

    Clicking off the "Compact Large Object" did not make a difference.

    When I right clicked on the indexes named from my query and clicked on options under General and saw a "Use page locks when accessing the index" check box.  When I clicked it on for both indexes, the problem went away.

    It seems that if you use a db made or designed for SQL 2005 in SQL 2008 you might run into this issue.  Hope this helps.
    Friday, January 16, 2009 6:35 PM
  • I'm also running into the problem where the index reorganization step of my maintenance plan is failing due to the page level locking error.   I have run the "SELECT * FROM sys.indexes WHERE allow_page_locks = 0" query and it returns records. However, we have several databases running on our SQL Server (2005) and I can't figure out what databases contain the tables with the indexes.  Any suggestion how to join the info returned by the sys.indexes query to something else that would provide me with the databases and/or tables that contain the indexes?

    For whatever it's worth, I have looked at the log generated by the maintenance plan and it doesn't output the specific database containing the table where the culprit index lives either.  Unfortunately.

    Monday, August 17, 2009 9:31 PM
  • There's probably a better way but the script below ultimately accomplished what I needed it to do - per the question I posted immediately above.  The summary of what it does is that it queries for all user databases on the system, which it uses to populate a cursor containing database names.  A loop is used to query the [database name].sys.indexes table for each database where allow_page_locks is 0, while not returning indexes (queue_clustered_index and queue_secondary_index) that seem to be common to all databases but that don't cause the DB optimization plan to fail.  The TSQL is below:

    DECLARE @DBName NVARCHAR(50)
    DECLARE @DynamicSQL NVARCHAR(300)

    DECLARE @DBCursor CURSOR
    SET @DBCursor = CURSOR FOR
     SELECT NAME FROM SYS.DATABASES
     WHERE NAME NOT IN ('master','tempdb','model','msdb')

    OPEN @DBCursor
    FETCH NEXT FROM @DBCursor INTO @DBName

    WHILE @@FETCH_STATUS = 0
     BEGIN
      SET @DynamicSQL = 'SELECT * FROM [' + @DBName + ']' + '.sys.indexes WHERE allow_page_locks = 0 AND name <> ''queue_clustered_index'' AND name <> ''queue_secondary_index'''
      PRINT @DynamicSQL
      EXEC SP_EXECUTESQL @DynamicSQL

      FETCH NEXT FROM @DBCursor INTO @DBName
     END

    CLOSE @DBCursor
    DEALLOCATE @DBCursor  

    Wednesday, August 26, 2009 5:04 PM
  • A small addition to LB20's script above, will create the ALTER statement for each index...

    drop table ##nonPageLockIndex
    go
    set nocount on
    DECLARE @DBName NVARCHAR(50)
    DECLARE @DynamicSQL NVARCHAR(400)

    DECLARE @DBCursor CURSOR
    SET @DBCursor = CURSOR FOR
    SELECT NAME FROM SYS.DATABASES
    WHERE NAME NOT IN ('master','tempdb','model','msdb')
    CREATE TABLE ##NonPageLockIndex ([Database] varchar(50) not null, [Table] varchar(100) not null, [Index] varchar(100) not null)
    OPEN @DBCursor
    FETCH NEXT FROM @DBCursor INTO @DBName
    SELECT  'use ' + @DBName  + 'GO'
    WHILE @@FETCH_STATUS = 0
     BEGIN
      SET @DynamicSQL = 'use ' + @dbname + ' SELECT ''' + @dbname + ''' [Database], object_name(object_id) [Table], name [Index] FROM [' + @DBName + ']' + '.sys.indexes WHERE allow_page_locks = 0 AND name <> ''queue_clustered_index'' AND name <> ''queue_secondary_index'''
      PRINT @DynamicSQL
      INSERT ##NonPageLockIndex ([Database],[Table],[Index]) EXEC SP_EXECUTESQL @DynamicSQL

      FETCH NEXT FROM @DBCursor INTO @DBName
     END

    CLOSE @DBCursor
    DEALLOCATE @DBCursor

    SELECT 'USE ' + [Database] + CHAR(10) +
    'ALTER INDEX '+ [Index] + ' ON ' + [Table] + ' SET (ALLOW_PAGE_LOCKS = ON )'
    FROM ##NonPageLockIndex
     

    Tuesday, January 17, 2012 4:58 PM
  • I used this script and seemed to be working OK but now I started to check if any indexes are still left with page lock 0 and yes...this script cannot change those!

    Checked using:

    SELECT * FROM sys.indexes WHERE allow_page_locks = 0

    I used the same script as above and removed this:

    AND name <> ''queue_clustered_index'' or name <> ''queue_secondary_index''

    Same result...indexes are not changed!

    Wednesday, August 12, 2015 2:17 PM
  • Can it be because the indexes not changed are non-unique,non-clustered?
    Wednesday, August 12, 2015 2:19 PM