SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Script to find all indexes where page level locking is disabled


Script to find all indexes where page level locking is disabled

Author
Message
JarJar
JarJar
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2095 Visits: 1132
FailedSad-1073548784) Executing the query "ALTER INDEX [ARFCRDATA~0] ON [bip].[ARFCRDATA] REO..." failed with the following error: "The index "ARFCRDATA~0" (partition 1) on table "ARFCRDATA" 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.


i'm trying to use a maintenance plan but i am hit with these errors. i'm sure there is a script that i can run to detect these guys so that i can skip them in the MP. or, as an option, turn ON page level locking for these tables, run the reindex/rebuild, then turn it back on.



PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13735 Visits: 4639
OLDCHAPPY (6/9/2013)
FailedSad-1073548784) Executing the query "ALTER INDEX [ARFCRDATA~0] ON [bip].[ARFCRDATA] REO..." failed with the following error: "The index "ARFCRDATA~0" (partition 1) on table "ARFCRDATA" 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.


i'm trying to use a maintenance plan but i am hit with these errors. i'm sure there is a script that i can run to detect these guys so that i can skip them in the MP. or, as an option, turn ON page level locking for these tables, run the reindex/rebuild, then turn it back on.


try this... SELECT * FROM sys.indexes WHERE allow_page_locks = 0;

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14067 Visits: 11848
There is probably very little advantage to having allow_row_locks or allow_page_locks disabled, so you would be better off finding them and fixing them.

select * from sys.indexes where allow_row_locks = 0 or allow_page_locks = 0


JarJar
JarJar
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2095 Visits: 1132
thanks. unfortunately its SAP so i can't change anything. it would be super cool if MS would make a more intelligent reindex/reorg task that could skip indexes with allow page locks.



GilaMonster
GilaMonster
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217991 Visits: 46278
Use something like Ola's index maintenance. Much better than the maint plan, rebuilds just what needs rebuilding and I think can handle the disabled page locks.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


JarJar
JarJar
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2095 Visits: 1132
oh man, the ola scripts are slick. :-)



Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14067 Visits: 11848
OLDCHAPPY (6/11/2013)
thanks. unfortunately its SAP so i can't change anything. it would be super cool if MS would make a more intelligent reindex/reorg task that could skip indexes with allow page locks.


In my experience, allow_row_locks = 0 or allow_page_locks = 0 are usually set that way by accident. In at least one version of SSMS, it seemed to get set that way by accident a lot.

You might try asking the vendor about that to see if it OK to make the change. There is probably at least a small chance that they won't give you the usual BS that you get from vendors when you point out an error. :-)
JarJar
JarJar
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2095 Visits: 1132
if it were anything else other than SAP i would pursue it. it's not worth the uphill battle and i'm def not doing it covertly. there's actually no perf problems right now, just trying to be proactive. there is so much daily loading / deleting going on in BI that it's really hard to stay on top of it. regular and fully update stats seems to be the better way to go.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search