Index maintenance, excluding index with page_level_lock disabled

Question

Post reply

Index maintenance, excluding index with page_level_lock disabled

rharderwijk

Ten Centuries

Points: 1119
More actions
May 28, 2013 at 3:22 am

#394858

Hi all,
I'm running into an issue with not being able to rebuild / reorganize an index because allow_page_locks is not enabled on certain indexes. The database was provided by a 3rd party, so changing the setting on the indexes is not really an option.
In the maintenance plan I changed the T-SQL code to exclude the tables where allow_page_locks is set to 0. When running the code against the database in a regular query, the indexes with the allow_page_locks set to 0 are not returned. When running the maintenance job, it fails on the same index that should be excluded. Below is my code, can anyone tell me what is going wrong here?
[font="Times New Roman"]--SELECT
-- object_id AS objectid,
-- index_id AS indexid,
-- partition_number AS partitionnum,
-- avg_fragmentation_in_percent AS frag
-- INTO #work_to_do
-- FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL)
-- WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
--
--new code below here
SELECT
a.object_id AS objectid,
a.index_id AS indexid,
a.partition_number AS partitionnum,
a.avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) a
inner join sys.indexes b
on a.object_id = b.object_id
WHERE b.allow_page_locks > 0 AND a.avg_fragmentation_in_percent > 10.0 AND a.index_id > 0;
--end new code[/font]
Cheer, Rick

Viewing 6 posts - 1 through 5 (of 5 total)

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

durai nagarajan SSCoach Points: 15778 More actions · Answer 1

Can you go through this.

http://www.sqlservercentral.com/Forums/Topic518826-360-1.aspx#bm518862

Regards
Durai Nagarajan

durai nagarajan SSCoach Points: 15778 More actions · Answer 2

one more

http://sqlserverpedia.com/blog/sql-server-bloggers/how-to-decide-on-index-allow_row_locks-and-allow_page_locks/

Regards
Durai Nagarajan

rharderwijk Ten Centuries Points: 1119 More actions · Answer 3

Durai,

Thank you. As I cannot change the database on a permanent basis, as we do not own the design of it, I may have to do a workaround of allowing the page locks, and then disallowing them after index maintenance has been completed.

What I do not understand yet, is why my script is still trying to modify the index on tables with allow_page_locks set to off (0), while I exclude them from the selection.

Cheers,

Rick

durai nagarajan SSCoach Points: 15778 More actions · Answer 4

allow_page_locks is a bit field try =1 and proceed

Regards
Durai Nagarajan

rharderwijk Ten Centuries Points: 1119 More actions · Answer 5

Changed the code to =1, but I still get the same error during the maintenance...

[font="System"]

The index "PK_TABLE" (partition 1) on table "DB_TABLE" cannot be reorganized because page level locking is disabled.[/font]

So it is still adding that index to the #work_to_do table even though running the select statement from my query windows does NOT return that table based on the setting allow_page_locks=1 .

What am I missing?

Cheers,

Rick