InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 00:20:01 GMT20http://www.sqlservercentral.com/Forums/Topic858215-266-1.aspxI 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 PostI got some attention from the big guns and hopefully resulted in the problem being resolved.Thank you for correcting me.Thu, 04 Feb 2010 05:23:33 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic858215-266-1.aspx[quote][b]Paul White (2/3/2010)[/b][hr][quote][b]Paul Randal (2/3/2010)[/b][hr]Skipping over all the Oracle nonsense...[/quote]:laugh: :w00t:[/quote]:Whistling::laugh:Thu, 04 Feb 2010 00:06:27 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic858215-266-1.aspx[quote][b]Paul Randal (2/3/2010)[/b][hr]Skipping over all the Oracle nonsense...[/quote]:laugh: :w00t:Wed, 03 Feb 2010 23:45:30 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic858215-266-1.aspxSkipping 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_INFOMSGSTable 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?ThanksWed, 03 Feb 2010 11:38:51 GMTPaul Randalhttp://www.sqlservercentral.com/Forums/Topic858215-266-1.aspxSorry 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...Wed, 03 Feb 2010 07:26:17 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic858215-266-1.aspx[quote][b]Welsh Corgi (2/3/2010)[/b][hr]Oracle using the same concept as the later versions of SQL Server.[/quote]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.Wed, 03 Feb 2010 07:22:04 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic858215-266-1.aspxOracle using the same concept as the later versions of SQL Server.Wed, 03 Feb 2010 06:34:45 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic858215-266-1.aspxNot 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.[code="sql"]DECLARE @tblName sysnameSET @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)[/code]Does ALTER INDEX ... REBUILD give the same error?Wed, 03 Feb 2010 06:29:37 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic858215-266-1.aspx[quote][b]Welsh Corgi (2/3/2010)[/b][hr]Please rtefer to the following link:http://www.cryer.co.uk/brian/oracle/howto_orcl_rbai.htm[/quote]How is an article on rebuilding indexes in Oracle relevant to a SQL Server index problem?Wed, 03 Feb 2010 06:15:39 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic858215-266-1.aspxPlease rtefer to the following link:http://www.cryer.co.uk/brian/oracle/howto_orcl_rbai.htmWed, 03 Feb 2010 05:53:36 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic858215-266-1.aspxThe 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.Wed, 03 Feb 2010 05:34:11 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic858215-266-1.aspxAll 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 1Possible 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 3Could 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 fieldsthe 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 allTue, 02 Feb 2010 12:55:05 GMTharveyrj