July 1, 2011 at 1:53 am
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.
July 2, 2011 at 8:25 am
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
Change is inevitable... Change for the better is not.
July 3, 2011 at 9:55 pm
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
March 23, 2012 at 12:40 pm
sisiralek (9/8/2008)
:)This is a good solutionSET 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!
April 15, 2013 at 4:42 am
I guess this link will be helpful 😀
http://connectsql.blogspot.in/2012/06/sql-server-script-to-fix-allowpagelocks.html 😎
Thanx,
Ronald
April 15, 2013 at 5:03 am
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
April 15, 2013 at 4:21 pm
True, but the link is still helpful to others that might find this thread.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply