August 20, 2008 at 7:51 am
We have a job to rebuild indexes which contains the following command to do the rebuild/reorg which is executing the following command:
SELECT @SQLString = 'ALTER INDEX ['+@IndexName+'] ON ['+@TableSchema+'].['+@TableName+'] REORGANIZE'
IF @PartitionNum > 1
SELECT @SQLString = @SQLString+' PARTITION = '+RTRIM(CONVERT(CHAR,@PartitionNum))
EXEC (@SQLString)]
One of the index rebuilds failed with the following error:
Msg 2552, Sev 16, State 1, Line 1 : The index "Cat2" (partition 1) on table "Cat_Int" cannot be reorganized because page level locking is disabled. [SQLSTATE 42000]
I found the the lockflags value in the sysindexes table for this index is set to 2 while the others which allow page level locking are set to 0. I thought I would just amend the cursor to add a check to see if the lockflag value was 0 and if the value was not 0, to skip. However, I wanted to be sure this would suffice. What are the actual values for the lockflag field in the sysindexes table and is this logic recommended?
August 20, 2008 at 8:31 am
Sysindexes is only included for backward compatibility, and shouldn't be used in new development.
Besides that, perhaps it will be easier to query the allow_row_locks and allow_page_locks columns in sys.indexes to get the allowable locking modes for the index.
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
August 20, 2008 at 10:46 am
How can I thank you enough? And I would have a nice day, but my back's killing me . . .
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply