Error 169 rebulding Index.

Anyone ever got this error ?

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

I am trying to rebuild a clustered index on a table and it
generates this error. I copied the table to another server and rebuild the index without any problems, I then made a copy of the table with a different name on the same server and rebuilt the index without any hassles. The error doesn't make sense given the context. I figured it must be a data corruption issue but I cannot find the problem. Any ideas ?

YorkieQ1 Any ideas ?

A1 What results do you get running:

dbcc Checktable (ProblemTableName) WITH ALL_ERRORMSGS

-- in single user Mode

dbcc Checktable (ProblemTableName, REPAIR_ALLOW_DATA_LOSS ) WITH ALL_ERRORMSGS

-- And if that turns up nothing:

dbcc CheckDB (ProblemTableName) WITH ALL_ERRORMSGS

-- in single user Mode
dbcc CheckDB (ProblemTableName, REPAIR_ALLOW_DATA_LOSS ) WITH ALL_ERRORMSGS|||I have tried some of your suggestions, I cannot at this stage run the single user mode queries but so far nothing has turned up.|||RE: So far nothing has turned up.

You might want to try scheduling the rebuild of the clustered index for a time when no-one would be accessing it (or check to see if any locks related to user activity that involves the table may be causing the behavior).|||The current table that my live application is using is fine, it doesn't have the error however in the process of finding the error I renamed the dud table and made a copy that is now the live table. The system is working but I need to try and find the cause of the problem.

We have some maintenance applications that do cleanup on the table to keep the system running smooth and they were the ones that started to crash, somehow I though of rebuilding the index on the table and that is how I found the problem. If I copied the table and created identical indexes on the new table then I had no problems but if I tried to rebuild the indexes on the dud table I got the errors, I still do.

Problem is that you cannot rebuild the indexes on the table without having the error, in future I know what to look for if the same errors start occuring but rebuilding indexes daily can be a costly exercise on busy tables?|||RE: Problem is that you cannot rebuild the indexes on the table without having the error, in future I know what to look for if the same errors start occuring but rebuilding indexes daily can be a costly exercise on busy tables?

True enough, I'd be concerned about the cause, locus, timing and frequency as well. DBCC INDEXDEFRAG is fairly gentle and may be of help. (It might fail on the bad page and provide some additional clues as well. From there maybe use dbcc page to investigate further?)

Does this happen often?|||To my knowledge this was the first time we caught it, we have had strange hassles before but because of the obscurity of the error I hadn't looked there before. The database is a big one, 20GB, well it's big in my experience and we move about 250 000 records through this table everyday, so it can be very busy. I have the feeling that this is the first time this problem has occured because in the past the other issues have resolved themselves but this one could not be fixed by just rebuilding an index.

I will run the index defrag on the dud table and see what turns up. One more thing which is strange is that even when I ran a insert (into another table) and delete on this 'dud' table is came up with the error 169. Go figure?

Is it worth putting sp3 on windows2000 with SQL2000 sp2?|||Also, I forgot to mention, use dbcc showcontig with dbcc indexdefrag (to provide a measure that lets one get to know when defragging is probably going to be useful).

How fragmented is the EvilClusteredIndex if you run showcontig? For example

Use Pubs
Go

DBCC SHOWCONTIG (Authors, UPKCL_auidind) WITH TABLERESULTS, ALL_LEVELS

DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES, ALL_LEVELS|||To my knowledge this was the first time we caught it, we have had strange hassles before but because of the obscurity of the error I hadn't looked there before. The database is a big one, 20GB, well it's big in my experience and we move about 250 000 records through this table everyday, so it can be very busy. I have the feeling that this is the first time this problem has occured because in the past the other issues have resolved themselves but this one could not be fixed by just rebuilding an index.

I will run the index defrag on the dud table and see what turns up. Good.

One more thing which is strange is that even when I ran a insert (into another table) and delete on this 'dud' table is came up with the error 169. Go figure? An interesting clue.
Is it worth putting sp3 on windows2000 with SQL2000 sp2?
The servers I run are there (some are also running the hotfix) but I don't think that will address your issue.|||Not defragmented at all, It might have been before I originally tried to recreate the index but I will never know now. When I run the query it scans 2054 pages and moves no pages. This is seriously one of those ghost in the machine stories, the one when you start a job as some who does not have a superstitious bone in your body and everyday you think evermore that the SQL box is after you. Superstitious Queerie Language. Still running tests.|||Not defragmented at all, It might have been before I originally tried to recreate the index but I will never know now.

I guess you droped i.e.(Drop EvilIndex or Alter Table drop index) and tried a fresh Create already?|||Had to. It was the last option I had. I still have the table with the evil index and it still produces the error when I try and rebuild the index so I can keep playing with it but fortunately I have another identical table without the error running live. Using DTS I copied the table and using query analyser I scripted the indexes on the old onto the new table, works like a dream, same data, no problem with index. Might be a bad spot on a disk? Wouldn't know where though because the filegroup is spread accross a few drives.|||I still have the table with the evil index and it still produces the error when I try and rebuild the index so I can keep playing with it but fortunately I have another identical table without the error running live.

Clearly corruption of some form, (though apparently undetectable by dbcc etc.), very unusual indeed; if you find out more please let us know.sql