June 9, 2013 at 11:53 pm
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.
June 10, 2013 at 9:30 am
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.June 10, 2013 at 10:52 am
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
June 11, 2013 at 3:50 am
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.
June 11, 2013 at 4:24 am
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
June 12, 2013 at 7:32 am
oh man, the ola scripts are slick. 🙂
June 12, 2013 at 10:13 am
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. 🙂
June 12, 2013 at 11:59 pm
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply