Sqlserver-dba.com

Page level locking is disabled

Upon executing a :
 Alter Index [UI_myindex] On [MyDb].[dbo].[Mytable] ReOrganize

the following error message was returned :

Msg 2552, Sev 16, State 1, Line 1 : The index "UI_myindex" (partition 1) on table "Mytable" cannot be reorganized because page level locking is disabled. [SQLSTATE 42000]

I issued the following SQL Statement on the relevant database :

Select A.Name as InName,ob.Name as DBName from sys.indexes A
left outer join sys.objects ob on ob.object_id=A.Object_id
where allow_page_locks=0 and ob.type='U'

and indeed this index was the only index with  :allow_page_locks=0


I discovered a developer had added the index via the SSMS , which by default has page_locks turned off.
They were not even aware that this was their intention

The first thing I did was change their security details - retsricting them from making ad-hoc index changes
Secondly , I turned page - locking on for this index .

USE [MYDB]
GO
ALTER INDEX [myIndex] ON [dbo].[myTable] SET ( ALLOW_PAGE_LOCKS  = ON )
GO

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact