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

Question

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

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

jon-230788 SSC Journeyman Points: 82 More actions · Answer 1

Odd. I have a indexes that actually looking at how they are defined are already set to ALLOW_PAGE_LOCKS = ON, but I still receive this error.

Altering the index setting prior to attempting to reorganize allows me to work around, but the index is already set to allow page locks.

Any answers to that one? Or is this another bug in 2005 SP3?

guna.vaishu Valued Member Points: 71 More actions · Answer 2

Hi,

Actually i work in Teradata. Dono much abt SQl server.

I have a query which is throwing the error

The index "xfer_id" (partition 1) on table "mdw_xfer_s2" cannot be reorganized because page level locking is disabled.

the query is:

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',

' + RTRIM(@indexid) + ') - fragmentation currently '

+ RTRIM(CONVERT(varchar(15),@frag)) + '%'

SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',

' + RTRIM(@indexid) + ')'

EXEC (@execstr)

FETCH NEXT

FROM indexes

INTO @tablename, @objectid, @indexid, @frag

END

while executing the second step it is throwing the above error.

Can you please tell how can i use the solution u provided in this?

thanks so much in advance. Im waiting for the reply badly.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

guna.vaishu (7/1/2011)
The index "xfer_id" (partition 1) on table "mdw_xfer_s2" [font="Arial Black"]cannot be reorganized because page level locking is disabled.[/font]

The error pretty much says it all. In order for the index to be reorganize, you going to have to, at least temporarily, all page level locking on the index.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tiggr Valued Member Points: 73 More actions · Answer 4

You *could* drop & re-create the index (which will implicitly re-organise it)...but that's "using a sledgehammer" and you'll possibly get a few unwanted side-effects e.g. lock everyone out of the table.

I'd also hazard a guess that the required disk space may be far more than for re-organising the index.

HTH

Andy

Eric Higgins Old Hand Points: 325 More actions · Answer 5

sisiralek (9/8/2008)
:)This 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,
Ranjith:)

This is great, thank you!

rock316ster SSC Rookie Points: 37 More actions · Answer 6

I guess this link will be helpful 😀

http://connectsql.blogspot.in/2012/06/sql-server-script-to-fix-allowpagelocks.html 😎

Thanx,

Ronald

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Please note: 7 year old thread

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 8

True, but the link is still helpful to others that might find this thread.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)