February 2, 2010 at 12:55 pm
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
February 3, 2010 at 5:34 am
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/
February 3, 2010 at 5:53 am
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/
February 3, 2010 at 6:15 am
Welsh Corgi (2/3/2010)
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
February 3, 2010 at 6:29 am
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
February 3, 2010 at 6:34 am
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/
February 3, 2010 at 7:22 am
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
February 3, 2010 at 7:26 am
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/
February 3, 2010 at 11:38 am
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
February 3, 2010 at 11:45 pm
Paul Randal (2/3/2010)
Skipping over all the Oracle nonsense...
:laugh: :w00t:
February 4, 2010 at 12:06 am
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
February 4, 2010 at 5:23 am
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