Database Maintenance Plan Failure

  • My DB Maintenance Plan has just started failing and I'm at a loss in trying to find out why. It fails on the "Rebuild Indexes" portion of it with Error Number: 169 and an Error Message of: [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 169:

    [Microsoft][ODBC SQL Server Driver][SQL Server] A column has been specified more than once in the order by list. Columns in the order by list must be unique.

    While I think I understand what the error is telling me, I'm unable (to date) to find the column(s) that are duplicated. Any suggestions?

    Thanks for any help.

  • Not sure of the error. Can you recreate the maintenance plan. I've had issues with these things failing and not really providing good error messages.

    Steve Jones

  • I've deleted the Maintenance Plan and created a new one and it generates the same error. So I'm pretty confident that it's truly the index that is the problem. It's just WHICH index is the problem? We've been adding indexes pretty aggressively lately and it's likely that one or more columns could have been duplicated. I'd just love to be able to find out which table carries the problem index.

    Thanks for the input.

  • The only thing I can think of is to script all indexes and then clean the results or write a quick parser to get the table name and columns. Then load this into a temp table and query it.

    Steve Jones

  • I thought of that, but was hoping there was a better way. But I'm with you, that might be the best option. Thanks again!

  • Good luck. If you build the script or process, fell free to post it. I might work on it if I have a chance.

    Steve Jones

  • I scripted out the indexes and did a Search and replace in QA to get down to

    "INDEX [Addr_NDX1] ON [dbo].[Addr]([CityID], [StateID])"

    I then Loaded this into a table and cleared it down to the columns index, table, col, using replace, charindex, and substring functions. Took about 15 minutes, but not that clean. I then used a quick cursor to "bend" the table and place each column on a separate line.

    If you want my notes, email me at Not that clean, but it worked.

    Steve Jones

  • Thank you VERY much. I'll take you up on that. I am going to dive into it more deeply this evening, but your help is greatly appreciated. Thanks again!

  • Glad to help. Hope it works and let us know.

    Steve Jones

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

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