The index “[IndexName]” on table “[TableName]” cannot be reorganized because page level locking is disabled.

Today, I have found one of the index maintenance jobs in a SQL Server 2005 instance has failed. The history shows a helpful message:

The index “[IndexName]” on table “[TableName]” cannot be reorganized because page level locking is disabled.

This error is caused by the index reorganization step in the attempt to defragment the index in question but the index has the page level locking option disabled. The job fails and the rest of the indexes will not get worked on. On the next subsequent scheduled run of the job, the same scenario will happen until you enable page locking for this index and the indexes with page level locking disabled. Yes. It is really a royal pain in the behind. You can’t just run away from it and hoping the problem will go away. You have to man up to understand and resolve the problem.

By default, page level locking is enabled for indexes. Usually, the page level locking for the indexes get disabled unintentionally either by third-party tools or by using RTM version of SQL Server 2005 and creating indexes via the GUI in the Object Explorer. In case, you know of any other way the page level locking get disabled for the indexes, please let me know.

To find all the indexes in the database that have page level locking disabled, you can run the following query:

-- indexes that have page level locking disabled
SELECT OBJECT_NAME(i.object_id) as TableName
, name as IndexName
, allow_page_locks
FROM sys.indexes as i
WHERE ALLOW_PAGE_LOCKS = 0

You can then submit a change request to have these indexes modified to enable page level locking.

To generate the code to alter the indexes to enable page level locking for all the required indexes, you can run the following query. This query will omit the internal system tables that do not have the page level locking for the indexes:

-- generate the TSQL Statments to enable page level locking for the required indexes
SELECT 'ALTER INDEX ' +i.Name+ ' ON ['
	+ts.TABLE_CATALOG+ '].[' +ts.TABLE_SCHEMA+ '].[' +ts.TABLE_NAME+']'
	+' SET (ALLOW_PAGE_LOCKS = ON)' as TSQL_Statement
FROM sys.indexes i LEFT OUTER JOIN sys.tables t
		ON i.object_id = t.object_id
	JOIN INFORMATION_SCHEMA.TABLES ts
		ON ts.TABLE_NAME = t.name
WHERE i.allow_page_locks = 0
	AND t.Name IS NOT NULL

Save the output and run it on the database in question and then try the index maintenance job again. This will solve the page level locking is disabled error.

 

REFERENCES:

sys.indexes
http://msdn.microsoft.com/en-us/library/ms173760(v=sql.105).aspx

sys.tables
http://msdn.microsoft.com/en-us/library/ms187406(v=sql.105).aspx

INFORMATION_SCHEMA.TABLES
http://msdn.microsoft.com/en-us/library/ms186224(v=sql.105).aspx

Be Sociable, Share!
Responses are currently closed, but you can trackback from your own site.

2 Responses to “The index “[IndexName]” on table “[TableName]” cannot be reorganized because page level locking is disabled.”

  1. Dayo says:

    Thanks great solution.It really helps a lot.

  2. [...] and then re-enable it after the maintenance is completed.  JP Chen has the scripts to do that here.  Note:  Haven’t tested those, but they look right.  I suspect you may need to exclude the [...]