Fix Index cannot be reorganized because page level locking is disabled error in SQL Server


June 15, 2013

Introduction

This article explains how to fix Index cannot be reorganized because page level locking is disabled error in SQL Server.

Problem

You may end up seeing the following error message Index cannot be reorganized because page level locking is disabled while running Alter index all on <table> ……. Reorganize TSQL Script.

Error Message

Msg 2552, Level 16, State 1, Line 1 The index "Index_Name" (partition 1) on table "Table_Name" cannot be reorganized because page level locking is disabled

Solution

A database administrator can resolve the above error message by enabling Page Level Locking on the index and then reorganize the index.

Newsletter Signup

Enable page level locking on index using SSMS

As shown in the below snippet select the checkbox Use page locks when accessing the index as highlighted and then save the changes before rebuilding the index.

Index cannot be reorganized because page level locking is disabled

Enable page level locking on index using TSQL command

ALTER INDEX <index name> ON <table name> SET (ALLOW_PAGE_LOCKS = ON)
GO

Share this Article


Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."