none
Error on Rebuilding Index

    Question

  • Hello,

    I have a SQL 2008 Standard Edition (10.0.6241) with multiple Databases (all having the same Setup, meaning same Tables, same Index and so on).

    During the last weeks, i have now seen twice that an Index is disabled and I cannot rebuild it (or set the in use Option in the gui), but this only happens in one of the databases (say, it doesnt work for database Project_10001 but it does work for every other db)

    running 

    ALTER INDEX [PK_tImportSkriptLog] ON [dbo].[tImportSkriptLog] REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = ON )
    GO


    will result in the error:

    Msg 0, Level 11, State 0, Line 0
    A severe error occurred on the current command.  The results, if any, should be discarded.

    however, like said, only for one of the databases, you can rebuild the index just fine on all the others. Running a DBCC CHECKDB says the Database is fine, I dont see any error in the eventlog or ERRORLOG. that would indicate something happened. Next thing I can try is to profile for ALTER INDEX Statements to try to find out what causes the disabling of the index in the first place but am curious if anyone got an idea on how to get more details on what goes wrong with the rebuild or what to try to get it enabled.

    Thanks

    Florian

    Friday, February 09, 2018 12:51 PM

Answers

  • turns out it was caused by setting a Default Connection Property (XACT_ABORT) for the SQL Server and unlike with e.g. QUOTED_IDENTIFIER, the error message in that case is unspecific. wrote my findings together in case anyone stumbles into the same problem in a short wiki article
    Monday, February 12, 2018 5:12 PM

All replies

  • https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql

    Using the ALL keyword with 'REBUILD PARTITION = ' 'Fails if the table has one or more:'

    Nonpartitioned index, XML index, spatial index, or disabled index

    If you remove the REBUILD PARTITION = ALL does it work for the disabled index?


    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.



    • Edited by KevinBHill Friday, February 09, 2018 1:01 PM
    Friday, February 09, 2018 1:01 PM
  • leaving the options and just using alter index [index] on [table] rebuild does produce the same error
    Friday, February 09, 2018 1:55 PM
  • turns out it was caused by setting a Default Connection Property (XACT_ABORT) for the SQL Server and unlike with e.g. QUOTED_IDENTIFIER, the error message in that case is unspecific. wrote my findings together in case anyone stumbles into the same problem in a short wiki article
    Monday, February 12, 2018 5:12 PM
  • Hi FZB,

    Thanks for your update on this post. Would you please mark your reply as answer so that other community members who have similar issue can benefit from it when they read this thread?

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 13, 2018 3:03 AM