May 15, 2008 at 2:27 am
Hi,
I am trying to reorganize all indexes on a database, but for some indexes I get the following error:
The index "ABC" (partition 1) on table "XYZ" cannot be reorganized because page level locking is disabled.
Now I know to enable page locking I execute:
ALTER INDEX ABC ON XYZ SET (ALLOW_PAGE_LOCKS = ON)
My questions are:
Why was page locking disabled in the first place? (SQL sets this option automatically right?)
What is the effect on my data and performance if I do Enable page locking on all indexes?
Any help please?
M
May 15, 2008 at 4:19 am
Apparently there was a bug that caused this option to be set to OFF whenever an index was created with management studio. This has been fixed in sp2.
The default behaviour is to set that option on so I'd go with the default behaviour as the SQL Server engine will decide whether it's appropriate to use it in each situation.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply