0

I have a maintenance plan that includes an index reorganization step. I am getting the following error in the job history:

Executing the query "ALTER INDEX [SpatialIndex-20180705-165942] ON [dbo..." failed with the following error: "The index "SpatialIndex-20180705-165942" on table "extended_index_113435478_384000" 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.

Now, yesterday I found the index with that name on a table that was NOT named "extended_index_113435478_384000." I changed the spatial index to allow both row and page locks. But I let the job run overnight and it still shows an error on the "extended index," which appears to be an internal table that I can't get at.

How to I resolve this error?

  • Have you checked out mssqltips.com/sqlservertip/4247/…? – Tony Hinkle Feb 13 at 18:08
  • 2
    Well you could consider Ola and tossing maintenance plans in the toilet. – Aaron Bertrand Feb 13 at 18:14
  • @TonyHinkle - Yes, I used the GUI to allow both row and page lock on the index named in the error, but the table that is named in the error is this "extended_index" table that I can't get at. – anon Feb 14 at 15:36
  • @anon As Aaron mentioned, Ola Hallengren's scripts are better than maintenance plans because they handle issues like this nicely. If it can't reorg, it rebuilds. So that would be the best way to solve this, and other issues that will likely arise in the future. Also, if this database is used by a third-party application I wouldn't recommend disabling page locks and whatnot on the tables and indexes. They may be set that way for a particular reason. – Tony Hinkle Feb 14 at 15:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.