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
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6990 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
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: Administrators
Points: 17067 Visits: 177
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
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 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
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 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
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 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
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6990 Visits: 930

Interesting ideas.

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

Barkingdog





noeld
noeld
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43594 Visits: 2052
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 (434K reputation)SSC Guru (434K reputation)SSC Guru (434K reputation)SSC Guru (434K reputation)SSC Guru (434K reputation)SSC Guru (434K reputation)SSC Guru (434K reputation)SSC Guru (434K reputation)

Group: General Forum Members
Points: 434508 Visits: 43506
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
SSC Eights!
SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)

Group: General Forum Members
Points: 900 Visits: 578
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 (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 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