If you’ve managed production database servers for any amount of time, you may have run into this error message in your maintenance plan logs. “The index “[IndexName]” on table “[TableName]” cannot be reorganized because page level locking is disabled.” This will cause your Index Reorganize step to fail. Fortunately, it’s pretty easy to fix.

First, a little bit of background. The root cause of the error is that the index reorganize step has hit an index that has page locking disabled. By default, and in most cases, page level locking should be enabled for indexes. It’s rare that one of your developers would intentionally turn off page level locking, but I suppose it’s possible. The more likely case is that one of the tools they’ve used has done it inadvertently. Lets look at how we can create an index using the ubiquitous SQL Server Management Studio.

First we’ll talk about the old standard, T-SQL:

CREATE INDEX Idx_JobNumber ON Jobs (JobNumber)

This will create a nonclustered index called Idx_JobNumber on the table Jobs. The index is based on the column JobNumber, in ascending order, by default. This method will create an index with page level locking enabled by default, as it probably should be. This works in SQL Server 2005 and 2008. I’d bet it works the same on 2000 as well, but I don’t have a 2000 instance handy to test it on.

Second, the UI (method 1):

It’s also possible to create an index by expanding the the table you wish to create the index on in the SSMS Object Explorer, and right clicking the Indexes tree, and selecting New Index. In older versions of Management Studio, such as the one bundled with SQL Server 2005, pre service packs, this index would actually be created with page level locking disabled by default. This setting can be configured in the options table on the new index dialog. This is typically how indexes with page level locking disabled get created. A developer coming onto a project with out of date tools is a common cause of this.

This is fixed in later service packs of SQL Server 2005 Management Studio. All versions of SQL Server 2008 Management Studio create the index with page level locking enabled by default.

Third, the UI (method 2):

You can get to yet another dialog to create an index by right clicking on a table in the object explorer and selecting Design. In the table design menu, right click and select Indexes/Keys. According to my research this method never exhibited the problem that the above method did. This is fortunate, but little consolation, since this is the least obvious method of creating an index anyhow.

Great, well, now that we’ve got that out of the way I’ll explain how you can locate these indexes and fix them.

We can locate the indexes that do not allow page locks by querying the sys.indexes table where allow_page_locks = 0.

SELECT *
FROM sys.indexes
WHERE ALLOW_PAGE_LOCKS = 0

That doesn’t tell us which tables these indexes are on. Unless you know your database inside and out, you probably need some more info. Lets try this query.

SELECT 'ALTER INDEX ' + I.Name + ' ON ' +  T.Name + ' SET (ALLOW_PAGE_LOCKS = ON)' As Command
FROM sys.indexes I
LEFT OUTER JOIN sys.tables T ON I.object_id = t.object_id
WHERE I.allow_page_locks = 0 AND T.Name IS NOT NULL

This query joins sys.indexes to sys.tables on the object_id column to give us the table that the index is on. Also, we’ve added the T.Name IS NOT NULL to filter out some internal tables that will show up with allow_page_locks = 0. We really don’t want to mess around with those.

Now we know which tables are our offenders. Lets write a query to do something about it. 

SELECT 'ALTER INDEX ' + I.Name + ' ON ' +  T.Name + ' SET (ALLOW_PAGE_LOCKS = ON)' As Command
FROM sys.indexes I
LEFT OUTER JOIN sys.tables T ON I.object_id = t.object_id
WHERE I.allow_page_locks = 0 AND T.Name IS NOT NULL

This query creates the necessary ALTER INDEX statements to correct the indexes we identified above. Run the generated commands to correct all of the indexes found above.

Your index reorganize maintenance plan steps should no longer fail. Of course, there any numerous other ways your maintenance plans could be bombing, but at least it’s not page level locking.