corrupt index (nonclustered primary key) after 2005 upgrade

  • All of the indexes corresponding to nonclustered primary keys in my SQL Server 2005 databases appear to be corrupt. Attempts to use DBCC DBREINDEX produce the following error...

    Msg 211, Level 23, State 5, Line 1

    Possible schema corruption. Run DBCC CHECKCATALOG.

    DBCC CHECKCATALOG and DBCC CHECKDB do not find any problems.

    I was able to rebuild the indexes prior to upgrade from SQL Server 2000 Standard to SQL 2005 Standard. It was not an "in-place upgrade", we moved database backups to the new server.

    I have been able to work around the problem in my development environment by dumping the records to a temp table, dropping the table and then reinserting all the records. Dropping the primary key/index alone will not work... i get the following error...

    Msg 3728, Level 16, State 1, Line 3

    'PK__mykey' is not a constraint.

    Msg 3727, Level 16, State 0, Line 3

    Could not drop constraint. See previous errors.

    I only noticed this because I was setting up a maintenance task to rebuild indexes occasionally and it failed. I am not noticing any performance issues.

    I can survive by dropping and recreating the tables as I mentioned above... but if someone has a better solution I would certainly appreciate it. I have many tables to repair across multiple databases and have to worry about causing downtime for my users.

    Other info...

    the primary keys are identity fields

    the database server and database operate in 'Latin1_General_BIN' collation.

    i am pretty certain this is not a hardware issue; i have restored a backup to 3 seperate sql server machines and the problem persists across them all

  • The Primary Key Contraints has a different name or it does not exist.

    Try querying the sys.ojects, sys.constarints & sys.columns tables. Also you the GUI to identify ihe constraints on a table.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Please rtefer to the following link:

    http://www.cryer.co.uk/brian/oracle/howto_orcl_rbai.htm

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Welsh Corgi (2/3/2010)


    Please rtefer to the following link:

    http://www.cryer.co.uk/brian/oracle/howto_orcl_rbai.htm%5B/quote%5D

    How is an article on rebuilding indexes in Oracle relevant to a SQL Server index problem?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Not sure what could be happening here, I've pinged a corruption expert to get some advice.

    Just one question first, pick a table that has this problem and run the following queries please and post the results.

    DECLARE @tblName sysname

    SET @tblName = 'LargeTable'

    SELECT name, type_desc FROM sys.indexes WHERE object_id = OBJECT_ID(@tblName)

    SELECT name from sys.key_constraints where parent_object_id = OBJECT_ID(@tblName)

    Does ALTER INDEX ... REBUILD give the same error?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Oracle using the same concept as the later versions of SQL Server.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Welsh Corgi (2/3/2010)


    Oracle using the same concept as the later versions of SQL Server.

    Maybe, but that doesn't mean that an article on how to rebuild Oracle indexes is going to help in the slightest with a problem rebuilding SQL indexes. The commands are different, the errors are different, the index structures and system table structures are different.

    I don't know what concept you were referring to, but SQL's been using b-tree indexes for many versions now.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Sorry for any confusion.

    The important thing is that you check the system tables to determine if the index exists. I mentioned this in a couple of post earlier today but Iunfortunately I did not mention this in this post.

    Also in the Link it was implied to use an ALTER INDEX Command.

    Thank you for correcting me and looking into and answering the Forum Member's Question.

    Regards...

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Skipping over all the Oracle nonsense...

    There's corruption somewhere in the obfuscated-in-2005 system tables.

    Can you try the following:

    DBCC CHECKTABLE (65) WITH ALL_ERRORMSGS, NO_INFOMSGS

    Table ID 65 is sysrowsetrefs, which is a partial replacement for syscomments in 2000.

    Did you get any errors during the upgrade when you restored the backups?

    Can you restore the backups on a 2000 server and run DBCC CHECKCATALOG and DBCC CHECKDB on them?

    Thanks

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Paul Randal (2/3/2010)


    Skipping over all the Oracle nonsense...

    :laugh: :w00t:

  • Paul White (2/3/2010)


    Paul Randal (2/3/2010)


    Skipping over all the Oracle nonsense...

    :laugh: :w00t:

    :Whistling::laugh:

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I regret making the dumb post very early in the morning.

    I provided an incorrect response.

    When I mention "Concept" I was thinking of the ALTER INDEX Command which is a new command and there are some new sDMV's in addition to the sysobjects table...

    Please pardon me or if you could or a stay of execution would be appreciated. 🙂

    I did not realize it was an Oracle post. I saw the ALTER INDEX Statement and I missed that it was an Oracle Post

    I got some attention from the big guns and hopefully resulted in the problem being resolved.

    Thank you for correcting me.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 12 posts - 1 through 11 (of 11 total)

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