"..table "ItemNum" cannot be reorganized because page level locking is disabled"
-
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.
-
This was removed by the editor as SPAM
-
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
-
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.
-
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.
-
Interesting ideas.
What is the "recommended" approach -- enabling page level locking for index reoroganizaiton or not?
Barkingdog
-
Page level Locking is "ON" by default.
You should disable it only on *very rare* cases.
Nice SSMS BUG catch by the way !!!!
* Noel -
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.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) -
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
-
:)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:)
Ranjith Lekamalage
MCITP SQL 2012 (BI Development) -
Thanks Ranjith. This was a great "quick fix" and now all our weekly database maintenance jobs are running sucessfully.
-
Exactly what i was after, many thanks!
-
Very nice! I was having the same issue with my "Reorganize Index Task". I added the above solution right to my maintenance plan, immediately before the Reorganize Index Task and it worked perfectly. It should also prevent any such issues from popping up down the road. Thanks.
E
-
Thanks for the quick fix,
I turned on locks
SET (ALLOW_PAGE_LOCKS = ON),
ran my job,
then switched it off again
SET (ALLOW_PAGE_LOCKS = OFF).
have fun
Ben:-D
-
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
Thanks, this was a timesaver.
Edit: I didn't see the second page of the thread and therefore didn't realize so many people had already thanked him for this. Sorry to revive an old thread.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply