March 19, 2011 at 7:36 am
I am trying to reorganize indexes using a maintenance plan in SSMS.
I am getting this message:
...cannot be reorganized because page level locking is disabled.
What can I do to fix this so that I can reorganize the indexes.
I have seen in other posts to do this:
ALTER INDEX index_name ON table_name SET (ALLOW_PAGE_LOCKS = ON)
Is there a more efficient way to do this for all tables or is this the way to go so that I can reoganize?
Thanks DBAs.
Patti
March 19, 2011 at 11:25 am
As the error says, page level locking on that index has been disabled (not the default setting). See if you can find out why.
If it has to be disabled, then you need to rebuild, not reorganise.
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
March 20, 2011 at 12:57 am
Patricia Johnson (3/19/2011)
I am trying to reorganize indexes using a maintenance plan in SSMS.I am getting this message:
...cannot be reorganized because page level locking is disabled.
What can I do to fix this so that I can reorganize the indexes.
I have seen in other posts to do this:
ALTER INDEX index_name ON table_name SET (ALLOW_PAGE_LOCKS = ON)
Is there a more efficient way to do this for all tables or is this the way to go so that I can reoganize?
Thanks DBAs.
Patti
page level locking is disabled
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
March 21, 2011 at 7:45 am
Thank you both very much. I like those scripts and plan to incorporate them into my sites.
March 21, 2011 at 8:08 am
Thanks for the report muthukkumaran, but as long as you keep the link in your post, they still get what they want!!
TIA for the edit.
March 22, 2011 at 6:06 am
Thanks Ninja's_RGR'us.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
March 22, 2011 at 7:45 am
Hey Muthukkumaran,
Your website link said to defrag indexes based on these conditions:
1. Fragmentation >30 AND PAGES>1000 then rebuild
2. Fragmentation between 15 to 30 AND PAGES>1000 then reorganize&updatestatistics
3. If the above two conditions are false then update the statistics
Do you have anything to help me identify when #1 is true, or when #2 is true.
I would like to run this as a step in a job. If the condition for #1 is true, then run your index defrag sql or if #2 is true, then run a index reorg sql followed by an update statistics sql, or if #3 is true then just update the statistics. That would be really cool if there something out there that I could use.
Thanks Muthukkumaran.
Patti
March 22, 2011 at 7:47 am
Update stat is missing from the latest version, but this is the best script I know at this point.
March 22, 2011 at 7:50 am
One option (far from the only one that exists) http://sqlfool.com/2010/04/index-defrag-script-v4-0/
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
March 22, 2011 at 7:51 am
GilaMonster (3/22/2011)
One option (far from the only one that exists) http://sqlfool.com/2010/04/index-defrag-script-v4-0/
Do you have a version that also handles the stats?
March 22, 2011 at 7:54 am
Not one of Michelle's. There are others that do, do not have links handy.
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
March 22, 2011 at 7:59 am
GilaMonster (3/22/2011)
Not one of Michelle's. There are others that do, do not have links handy.
Alright, I was wondering if you have taken that task on and had something tested.
Since I have a small db, I can do a full update with full scan in only minutes so I didn't bother coding something smarter.
March 22, 2011 at 8:05 am
Ninja's_RGR'us (3/22/2011)
GilaMonster (3/22/2011)
Not one of Michelle's. There are others that do, do not have links handy.Alright, I was wondering if you have taken that task on and had something tested.
I did a couple years back, but the script was specific to a server, tied to the databases there and with logic based on the business processes.
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
March 22, 2011 at 8:12 am
GilaMonster (3/22/2011)
Ninja's_RGR'us (3/22/2011)
GilaMonster (3/22/2011)
Not one of Michelle's. There are others that do, do not have links handy.Alright, I was wondering if you have taken that task on and had something tested.
I did a couple years back, but the script was specific to a server, tied to the databases there and with logic based on the business processes.
So so back to waiting for the next version of the script ;-).
March 22, 2011 at 8:14 am
Ninja's_RGR'us (3/22/2011)
GilaMonster (3/22/2011)
Ninja's_RGR'us (3/22/2011)
GilaMonster (3/22/2011)
Not one of Michelle's. There are others that do, do not have links handy.Alright, I was wondering if you have taken that task on and had something tested.
I did a couple years back, but the script was specific to a server, tied to the databases there and with logic based on the business processes.
So so back to waiting for the next version of the script ;-).
Interesting, I had the beta version of 4.0 and it had update stats in there. The bad thing about it was that it updated all the stats on all tables in a database that was touched. I am working through making that more selective soon and will share that back with Michelle. Now all I need is time..... 😛
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply