May 28, 2009 at 1:15 pm
Hi,
We are upgrading from sql server 2000 to 2005 using side-by-side method. For this
1.I backed the databases in SQL Server 2000 and copied to new SQL Server 2005
2.Restoring the databases in SQL Server 2005, giving the following warnings about the Index corruption. IS this normal or do I need to do anything?
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 4464 pages for database 'PROD', file 'PROD_Master_dat' on file 1.
Processed 1 pages for database 'PROD', file 'PROD_Master_log' on file 1.
Converting database 'PROD' from version 539 to the current version 611.
Database 'PROD' running the upgrade step from version 539 to version 551.
Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_ctlRenewal_636F8578" (index_id = 3) on object ID 1668253048 in database "PROD". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_ctl_mstID_636F8578" (index_id = 4) on object ID 1668253048 in database "PROD". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_ctl_staNumber_636F8578" (index_id = 5) on object ID 1668253048 in database "PROD". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_ctlEffDate_636F8578" (index_id = 6) on object ID 1668253048 in database "PROD". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_ctlEnabled_636F8578" (index_id = 8) on object ID 1668253048 in database "PROD". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_ctl_typID_636F8578" (index_id = 9) on object ID 1668253048 in database "PROD". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_ctl_rlvID_636F8578" (index_id = 10) on object ID 1668253048 in database "PROD". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_ctlDescription_636F8578" (index_id = 11) on object ID 1668253048 in database "PROD". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Database 'PROD' running the upgrade step from version 551 to version 552.
Database 'PROD' running the upgrade step from version 552 to version 553.
Database 'PROD' running the upgrade step from version 553 to version 554.
Database 'PROD' running the upgrade step from version 554 to version 589.
Database 'PROD' running the upgrade step from version 589 to version 590.
Database 'PROD' running the upgrade step from version 590 to version 593.
Database 'PROD' running the upgrade step from version 593 to version 597.
Database 'PROD' running the upgrade step from version 597 to version 604.
Database 'PROD' running the upgrade step from version 604 to version 605.
Database 'PROD' running the upgrade step from version 605 to version 606.
Database 'PROD' running the upgrade step from version 606 to version 607.
Database 'PROD' running the upgrade step from version 607 to version 608.
Database 'PROD' running the upgrade step from version 608 to version 609.
Database 'PROD' running the upgrade step from version 609 to version 610.
Database 'PROD' running the upgrade step from version 610 to version 611.
RESTORE DATABASE successfully processed 4465 pages in 1.406 seconds (26.010 MB/sec).
I ran DBCC CHECKTABLE (TableName), giving NO errors..
please advice me..
May 28, 2009 at 1:34 pm
All of the _WA_% indexes are system generated and shouldn't be a problem. I would run the following to make sure there are no additional problems with the database. This is something you need to do once after upgrading anyways.
DBCC CHECKDB(database) WITH DATA_PURITY;
Once the above completes successfully, you can then run regular integrity checks.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2009 at 1:34 pm
You should be fine but run DBCC UPDATEUSAGE and DBCC CHECKTABLE to be sure.
More info here:
http://www.sqlservercentral.com/Forums/Topic655130-146-1.aspx
May 28, 2009 at 1:36 pm
On SQL 2000, drop all of those statistics (the names are given in the error message)
DROP STATISTICS <Statistics Name>
Once you've done that, run both of these commands on the SQL 2000 database (On SQL 2000 CheckDB did not include CheckCatalog)
DBCC CHECKCATALOG (< Database Name > )
DBCC CHECKDB (< Database Name > ) WITH NO_INFOMSGS, ALL_ERRORMSGS
If you get any errors, post them here.
If it's clean, then take another backup and try again to restore to SQL 2005.
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
May 28, 2009 at 5:05 pm
Thank You,
On SQL Server 2000, performed the below steps
1. DROP STATISTICS TableName.Statistics Name
2. DBCC CHECKCATALOG (PROD) -> No errors
3. DBCC CHECKDB (PROD) WITH NO_INFOMSGS, ALL_ERRORMSGS -> No errors
4. Backed the PROD database in SQL Server 2000
5.Restored in SQL Server 2005. Now NO ERRORS.
So, Iam good go with upgrade right?
Thanks
May 29, 2009 at 1:50 am
Sounds like you're good to go.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply