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

Dev

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

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.

 

More About the Author

Brian Bickell

Principal | Data Practice Lead
Tableau Driver’s Ed: Building a Partnership Tableau Driver’s Ed is a new blog series in which we explain InterWorks’ unique take on Tableau Drive, a new methodology ...
Tableau Driver’s Ed: Agile Development Tableau Driver’s Ed is a new blog series in which we explain InterWorks’ unique take on Tableau Drive, a new methodology ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072