Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script to find all indexes where page level locking is disabled Expand / Collapse
Author
Message
Posted Sunday, June 9, 2013 11:53 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:59 AM
Points: 319, Visits: 589
Failed-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.



Post #1461391
Posted Monday, June 10, 2013 9:30 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
OLDCHAPPY (6/9/2013)
Failed-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.
Post #1461620
Posted Monday, June 10, 2013 10:52 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:25 PM
Points: 3,121, Visits: 11,397
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

Post #1461675
Posted Tuesday, June 11, 2013 3:50 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:59 AM
Points: 319, Visits: 589
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.



Post #1461968
Posted Tuesday, June 11, 2013 4:24 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 42,443, Visits: 35,498
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 2008, MVP
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

Post #1461978
Posted Wednesday, June 12, 2013 7:32 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:59 AM
Points: 319, Visits: 589
oh man, the ola scripts are slick.


Post #1462628
Posted Wednesday, June 12, 2013 10:13 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:25 PM
Points: 3,121, Visits: 11,397
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.



Post #1462711
Posted Wednesday, June 12, 2013 11:59 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:59 AM
Points: 319, Visits: 589
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.




Post #1462907
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse