Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


corrupt index (nonclustered primary key) after 2005 upgrade


corrupt index (nonclustered primary key) after 2005 upgrade

Author
Message
harveyrj
harveyrj
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 10
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
Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5216 Visits: 4876
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/
Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5216 Visits: 4876
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/
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47427 Visits: 44405
Welsh Corgi (2/3/2010)
Please rtefer to the following link:

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

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


GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47427 Visits: 44405
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


Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5216 Visits: 4876
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/
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47427 Visits: 44405
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


Welsh Corgi
Welsh Corgi
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5216 Visits: 4876
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/
Paul Randal
Paul Randal
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2173 Visits: 1714
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 White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10352 Visits: 11350
Paul Randal (2/3/2010)
Skipping over all the Oracle nonsense...

Laugh w00t



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search