Post reply

Cannot reorganize an index because its disabled, but its not disabled.

  • July 11, 2018 at 7:17 am

    #407172

    I am attempting to reorganize an index, but I get an error message in SSMS that " Cannot perform the specified operation on disabled index '<index name>' on table '<table name>'

    I have verified that the index is not disabled, manually rebuilt it, and even dropped and created it for good measure. It still fails because SQL thinks the index is disabled. None of the other indexes on the table are disabled. Prior to this index failing to reorganize, a different index (a non clustered unique index) on this table had this same error, and dropping and creating it fixed that one, but not this one.

    I found this Article mentioning that constraints and FK's need to be manually enabled after disabling an index: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/disable-indexes-and-constraints?view=sql-server-2017
    So I checked the constraints on the table, and made sure they were enabled via this script:
    alter table <table name> check constraint all

    There are also no foreign keys referencing this table.

    I'm obviously missing something here, Not sure what to do on this. Would I need to drop and create all the indexes on the table to solve this?

  • July 11, 2018 at 11:13 am

    #1997166

    Did you verify that all the constraints are "trusted"?  If not trusted, perhaps that could cause an issue.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • July 11, 2018 at 11:25 am

    #1997169

    ScottPletcher - Wednesday, July 11, 2018 11:13 AM

    Did you verify that all the constraints are "trusted"?  If not trusted, perhaps that could cause an issue.

    Thanks for the response.  I had not checked that, and will check it out.  I did get it to work about 15 minutes ago however.  1 of 2 things did the trick.
    1.) I dropped and created the PK on the table (i missed this when i was verifying constraints and FK's)
    2.) rebuilt all indexes on the table.

    Unfortunately I didn't try the reorganize between the above 2 troubleshooting steps, so I am not sure which one did the trick, but i am leaning towards the PK.  I'll have to wait to see if further indexes on the table fail for this reason to be sure its fixed.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply