Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

corrupt index (nonclustered primary key) after 2005 upgrade Expand / Collapse
Author
Message
Posted Tuesday, February 2, 2010 12:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 2, 2012 6:40 AM
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

Post #858215
Posted Wednesday, February 3, 2010 5:34 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 9:19 AM
Points: 4,228, Visits: 4,280
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/

Post #858544
Posted Wednesday, February 3, 2010 5:53 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 9:19 AM
Points: 4,228, Visits: 4,280
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/

Post #858555
Posted Wednesday, February 3, 2010 6:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:34 AM
Points: 43,014, Visits: 36,173
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 2008, MVP
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

Post #858571
Posted Wednesday, February 3, 2010 6:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:34 AM
Points: 43,014, Visits: 36,173
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 2008, MVP
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

Post #858584
Posted Wednesday, February 3, 2010 6:34 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 9:19 AM
Points: 4,228, Visits: 4,280
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/

Post #858589
Posted Wednesday, February 3, 2010 7:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:34 AM
Points: 43,014, Visits: 36,173
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 2008, MVP
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

Post #858645
Posted Wednesday, February 3, 2010 7:26 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 9:19 AM
Points: 4,228, Visits: 4,280
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/

Post #858651
Posted Wednesday, February 3, 2010 11:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:05 AM
Points: 2,039, Visits: 1,665
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
Post #858905
Posted Wednesday, February 3, 2010 11:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:59 AM
Points: 11,194, Visits: 11,166
Paul Randal (2/3/2010)
Skipping over all the Oracle nonsense...





Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #859354
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse