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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |