Error on Rebuilding Index
Question
Friday, February 9, 2018 12:51 PM
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
All replies (4)
Monday, February 12, 2018 5:12 PM âś…Answered | 1 vote
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
Friday, February 9, 2018 1:01 PM
/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.
Friday, February 9, 2018 1:55 PM
leaving the options and just using alter index [index] on [table] rebuild does produce the same error
Tuesday, February 13, 2018 3:03 AM
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.