SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


"..table "ItemNum" cannot be reorganized because page level locking is disabled"


"..table "ItemNum" cannot be reorganized because page level locking is disabled"

Author
Message
Barkingdog
Barkingdog
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10176 Visits: 930

I get this error message when I attempt to backup a sql 2005 database. The databse is set to Simple recovery mode and I used the SSMS Database Maintenance Wizard to create the job. Any idea how to "enable page level locking?"

TIA,

Bill

Executing the query "ALTER INDEX [idx_ItemNum] ON [dbo].[ItemNum]
REORGANIZE WITH ( LOB_COMPACTION = ON )
" failed with the following error: "The index "idx_ItemNum" (partition 1) on
table "ItemNum" cannot be reorganized because page level locking is disabled.".
Possible failure reasons: Problems with the query, "ResultSet" property not
set correctly, parameters not set correctly, or connection not established correctly.





Site Owners
Site Owners
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: Administrators
Points: 20793 Visits: 276
No one has responded to this topic yet. Even if you don't have a complete answer, the original poster will appreciate any thoughts you have!
Patrick SCHNEIDER
Patrick SCHNEIDER
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 20
Page level locking can be set using the CREATE INDEX or ALTER INDEX statements :
ALTER INDEX index_name ON table_name SET (ALLOW_PAGE_LOCKS = ON)

The allow_page_locks column of sys.indexes view may help you find indexes having page level locking disabled.

Hope this helps.

Patrick
Maurice Pelchat
Maurice Pelchat
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 63

The problem is that I have also this porblem that appears spuriously from differents index on my development box.

I suspect a bug in the SQL Server management Studio, which turns the option on, when some other modifications are done to an index. This option is available from SSMS but hard to find, and the programmer who worked with the index was not even aware of it existance.

So even when watching carefully what happens we don't see where the option gets turned on.

I'm wondering if i could activate some DDL trigger to get prompted when this happen. Could be a solution.

I use SP2 latest with patches.


Maurice Pelchat
Maurice Pelchat
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 63

Found why....

By checking more how index are created by my developers, if found that one method for index creation is setting page level locking to on by default and the other is setting it to off.

If you set right click on index folder under a specific table, and you select new index, the option is set to off by default in index creation dialog. You have to go to the options page to turn it on.

If you go through the table context menu and select modify, to add an index, then the option is ON by default for the new index created.

I suspect that a lot of people are going to wonder why reorganize fail spuriously on some index on development boxes. Here you have an hint.


Barkingdog
Barkingdog
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10176 Visits: 930

Interesting ideas.

What is the "recommended" approach -- enabling page level locking for index reoroganizaiton or not?

Barkingdog





noeld
noeld
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61250 Visits: 2054
Page level Locking is "ON" by default.

You should disable it only on *very rare* cases.

Nice SSMS BUG catch by the way !!!!


* Noel
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)

Group: General Forum Members
Points: 619987 Visits: 45201
Is it a bug or did the boys in Redmond just decide to change a default like they did with the CONCATENATE NULL YIELDS NULL setting?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
shashi kant
shashi kant
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1208 Visits: 593
Thanks Patrick Schneider
Nice solution for this problem and your Provide solution
ALTER INDEX index_name ON table_name SET (ALLOW_PAGE_LOCKS = ON)

is working fine in my case.

Thanks & Regards
Shashi Kant chauhan
sisiralek
sisiralek
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 188
SmileThis is a good solution

SET NOCOUNT ON
DECLARE @DBName nvarchar(50), @INName nvarchar(50)
DECLARE @ODBName nvarchar(50), @OINName nvarchar(50)
Declare @execstr nvarchar(200)
--PRINT '-------- Vendor Products Report --------'
DECLARE Index_cursor CURSOR FOR
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'
-- Select only allow_page_locks 0 and User Tables
OPEN Index_cursor
FETCH NEXT FROM Index_cursor
INTO @INName, @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @DBName +' ' + @INName
--PRINT @INName
SET @ODBName = ltrim(rtrim(@DBName))
SET @OINName = ltrim(rtrim(@INName))
SELECT @execstr = 'ALTER INDEX '+@OINName+ ' ON '+
@ODBName+' SET (ALLOW_PAGE_LOCKS = ON)';
EXEC (@execstr);
FETCH NEXT FROM Index_cursor
INTO @INName, @DBName
END
CLOSE Index_cursor
DEALLOCATE Index_cursor

Cheers,
RanjithSmile

Ranjith Lekamalage
MCITP SQL 2012 (BI Development)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search