Over a million developers have joined DZone.

Index Page Level Locking is Disable ERROR

· Java Zone

Microservices! They are everywhere, or at least, the term is. When should you use a microservice architecture? What factors should be considered when making that decision? Do the benefits outweigh the costs? Why is everyone so excited about them, anyway?  Brought to you in partnership with IBM.

Introduction

When we are working with a production database, sometimes we find a specific message in maintenance plan log.

The index [IndexName] on table [TableName]

cannot be reorganized because page level locking is disabled. 

This will cause our Index Reorganize steps to fail. In this article, we are trying to explain it and solve it.

Why this Error Occurs

By default, the page level locks should be enabled for Index. The main cause of this error is that the Index Reorganize step has hit an Index that has page lock disabled. This somehow your Index Page Lock is disabled.

How to Solve it

Finding the Table Name and Index Name where Page Lock is Disabled

 SELECT T.Name AS [Table Name], I.Name As [Index Name]
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;
Now Use Alter Statement to Allow Page Lock On
The syntax is mentioned below
 ALTER INDEX <Index_Name> ON <Table_Name>
SET (ALLOW_PAGE_LOCKS = ON);
To generate the Alter Script Automatically
 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;
Hope you like it.

Discover how the Watson team is further developing SDKs in Java, Node.js, Python, iOS, and Android to access these services and make programming easy. Brought to you in partnership with IBM.

Topics:

Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}