Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2002
    Posts
    9

    Question Unanswered: 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 ?

    Yorkie

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Question Re: Error 169 rebulding Index.

    Q1 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

  3. #3
    Join Date
    Dec 2002
    Posts
    9
    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.

  4. #4
    Join Date
    Oct 2002
    Posts
    369
    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).

  5. #5
    Join Date
    Dec 2002
    Posts
    9
    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?

  6. #6
    Join Date
    Oct 2002
    Posts
    369
    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?

  7. #7
    Join Date
    Dec 2002
    Posts
    9
    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?

  8. #8
    Join Date
    Oct 2002
    Posts
    369

    Question

    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
    Last edited by DBA; 12-10-02 at 04:05.

  9. #9
    Join Date
    Oct 2002
    Posts
    369
    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.

  10. #10
    Join Date
    Dec 2002
    Posts
    9
    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.

  11. #11
    Join Date
    Oct 2002
    Posts
    369
    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?
    Last edited by DBA; 12-10-02 at 04:39.

  12. #12
    Join Date
    Dec 2002
    Posts
    9
    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.

  13. #13
    Join Date
    Oct 2002
    Posts
    369
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •