dbcc dbreindex returns error

Question

dbcc dbreindex returns error

Max-146500

SSCarpal Tunnel

Points: 4691
More actions
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

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

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

Edward McGovern Ten Centuries Points: 1299 More actions · Answer 1

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.

Max-146500 SSCarpal Tunnel Points: 4691 More actions · Answer 2

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

Edward McGovern Ten Centuries Points: 1299 More actions · Answer 3

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

Edward McGovern Ten Centuries Points: 1299 More actions · Answer 4

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

Max-146500 SSCarpal Tunnel Points: 4691 More actions · Answer 5

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

Max-146500 SSCarpal Tunnel Points: 4691 More actions · Answer 6

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

mstaple99 SSC Rookie Points: 25 More actions · Answer 7

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?