September 13, 2012 at 9:07 am
I have a database that has a table with an index where page level locking has been set to 'No'. A weekly index reorganize fails every week with an error message stating that it "cannot be reorganized because page level locking is disabled."
This DB is a custom system that was written many years ago. I don't have much info about it. I suspect it was a SQL2000 db and then upgraded to SQL2005, which is the current version. I wonder if simply dropping the index manually and recreating with default settings would solve the problem.
Any thoughts? Thanks.
Joe
September 13, 2012 at 9:34 am
No need to drop the index. ALTER INDEX and set ALLOW_PAGE_LOCKS to ON. Exact syntax is in Books Online.
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
September 13, 2012 at 11:21 am
Excellent. Thank you.
May I ask when disallowing page level locking might be appropriate?
September 13, 2012 at 11:49 am
Not very often.
It's briefly discussed in chapter 6 of http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
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
September 13, 2012 at 2:48 pm
Thanks, and thank you for the book reference. I downloaded a PDF. Very handy.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply