Post reply

dbcc dbreindex returns error

  • June 9, 2004 at 4:48 am

    #163887

    Error message:

    Server: Msg 169, Level 15, State 2, Line 2

    A column has been specified more than once in the order by list. Columns in the order by list must be unique.

    Situation:

    The table in question is part of a maintenance plan, the log has revealed the above error.

    Solutions tried:

    Rebuilding indexes manually - causes same problem, however, INDEXDEFRAG and UPDATEUSAGE work fine

    Delete all indexes and statistics, recreate indexes, rebuild indexes and viola! same problem, amazing.

    Check sysindexes, update column pgmodctr = 0 (as this was previously 151 and the only "index" (seeing as there weren't any actual indexes left at this time) with this value), rebuild index and unfortunately that didn't help either.

    If anyone knows what the error message actually means or how to fix it or know if I need to sit back and relax cause there aint nothing wrong, please let me know.

    Thanks

    Max

     

    Max

  • June 9, 2004 at 8:57 am

    #509664

    Error 169 is A column has been specified more than once in the order by list. Columns in the order by list must be unique. Which is odd. Usually you get that error in query.

    1) What does the table structure look like? Are there weird or non standard column names?

    2) Have you done a dbcc checktable to see if there is any corruption in the table?

    3) For sanities sack, I would recreate the table with a different name and no data then create the index and see if the reindex works.

    I have a sneaking feeling its naming or table corruptions.

    Hope this helps.

  • June 9, 2004 at 9:47 am

    #509680

    Ed,

    a most interesting notion, I feel obliged to join you in having sneaky feelings (about this database) but, in this case, the structure is sound and the table and indexes are not corrupt or defragmented.

    As per your suggestion I rebuilt the table, imported a couple of hundred k records and recreated the index. It works, but I have my reservations about having to do this on a regular basis.

    Do you perhaps know of any system objects that mark the table/index as suspect and what will be the complications be if I hit it with a mallet  (explicitly modify them)?

    Max

    Max

  • June 9, 2004 at 11:24 am

    #509695

    Could you post the create statement for the table and index?

  • June 9, 2004 at 11:31 am

    #509699

    It sounds data related. I just want to see what data types we are talking about in key indexes...

  • June 10, 2004 at 3:09 am

    #509795

    Thanks for you help so far.

    The table has six columns and I don't think it's got to do with the data or the table structure. I can't help but deduct that if that was the case the same error would have occured when I rebuilt it, if anything it's a meta data thing.

    I think the database stubbed it's toe or it's simply that time of the month and well as we all know things do go funny then.

    Max

  • June 14, 2004 at 9:38 am

    #510313

    Although I seem to have established that the error is not related to the table structure or the actual data, I haven't found a solution yet. Has anyone else come across this problem before?

    Max

  • December 1, 2004 at 4:26 pm

    #532120

    I am getting the same error on 2 tables when I run an SP_updatestats on a db.  In my case I removed a few unnecessary indexes and now I am getting the error.  My suspicion is that the system tables meta data got mixed up but I don't know what or how has anyone gotten an further on this one?

Viewing 8 posts - 1 through 7 (of 7 total)

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