July 26, 2011 at 8:51 am
Hi All,
I've restored SQL 2000 database on SQL 2005 server with Latin1_General_CI_AS standard collation at source and destination. DBCC runs fine on SQL 2000 server
But DBCC is resulting in error on SQL 2005 server
Msg 3858, Level 16, State 1, Line 1
The attribute (collation_name=53256) of row (object_id=1488984631,column_id=12) in sys.columns has an invalid value.
Msg 3858, Level 16, State 1, Line 1
The attribute (collation_name=53256) of row (object_id=1488984631,column_id=14) in sys.columns has an invalid value.
Msg 3858, Level 16, State 1, Line 1
The attribute (collation_name=53256) of row (object_id=1488984631,column_id=15) in sys.columns has an invalid value.
Msg 3858, Level 16, State 1, Line 1
The attribute (collation_name=53256) of row (object_id=1488984631,column_id=16) in sys.columns has an invalid value.
Msg 3858, Level 16, State 1, Line 1
The attribute (collation_name=53256) of row (object_id=1493580359,column_id=2) in sys.columns has an invalid value.
Msg 3858, Level 16, State 1, Line 1
The attribute (collation_name=53256) of row (object_id=1509580416,column_id=1) in sys.columns has an invalid value.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3858, State 1: The attribute (collation_name=53256) of row (object_id=1509580416,column_id=1) in sys.columns has an invalid value.
The query has exceeded the maximum number of error messages. Only the first 1000 messages will be displayed.
CHECKDB found 0 allocation errors and 1634 consistency errors not associated with any single object.
I'm bit worried here as we are going live soon
I need a quick fix ,please help.
Regards,
July 26, 2011 at 9:03 am
DBCC runs clean on SQL 2000 because it does not run CheckCatalog. CheckDB on SQL 2005 does.
Back on the SQL 2000 server, run this
DBCC CheckCatalog
Post the results.
There is no quick or easy fix here. It's going to be either very difficult or very time-consuming or both, or not possible.
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
July 26, 2011 at 9:48 am
The attribute (collation_name=53256) of row (object_id=1950070133,column_id=88) in sys.columns has an invalid value.
Msg 3858, Level 16, State 1, Line 1
The attribute (collation_name=53256) of row (object_id=1950070133,column_id=89) in sys.columns has an invalid value.
Msg 3858, Level 16, State 1, Line 1
The attribute (collation_name=53256) of row (object_id=1950070133,column_id=90) in sys.columns has an invalid value.
Msg 3858, Level 16, State 1, Line 1
The attribute (collation_name=53256) of row (object_id=1950070133,column_id=91) in sys.columns has an invalid value.
Msg 3858, Level 16, State 1, Line 1
The attribute (collation_name=53256) of row (object_id=1950070133,column_id=92) in sys.columns has an invalid value.
Msg 3858, Level 16, State 1, Line 1
The attribute (collation_name=53256) of row (object_id=1950070133,column_id=93) in sys.columns has an invalid value.
Msg 3858, Level 16, State 1, Line 1
The attribute (collation_name=53256) of row (object_id=1950070133,column_id=94) in sys.columns has an invalid value.
Msg 3858, Level 16, State 1, Line 1
The attribute (collation_name=53256) of row (object_id=1950070133,column_id=95) in sys.columns has an invalid value.
The query has exceeded the maximum number of error messages. Only the first 1000 messages will be displayed.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
July 26, 2011 at 9:52 am
Gail asked to run this on sql 2000. This seems to be sql 2K5.
How long has the DB been in production on sql 2K5?
July 26, 2011 at 9:58 am
That is NOT from SQL 2000. That's a SQL 2005 CheckCatalog output. Saw that already, don't need it.
Back on the SQL 2000 server, run this
DBCC CheckCatalog
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
July 26, 2011 at 10:09 am
It's still not in production
Output On SQL 2000 server
dbcc checkcatalog ('SALES')
Go
DBCC results for 'SALES'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
July 26, 2011 at 10:54 am
Pity. If it appeared as an error on SQL 2000 it would be fixable...
Script all objects, export all data, recreate the database.
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
July 26, 2011 at 1:03 pm
Thanks Gail.
But I'm still not able to understand how these errors are generated and what is wrong with the data on SQL 2005?
Is there anything wrong with Schema? Because everything is alright on SQL 2000
What is the risk of still going ahead with these errors?
Thanks in advance.
July 26, 2011 at 1:08 pm
I'm not sure I understand the real issue with that error (Gail's the real expert on any corrution around here).
To my little self it just seems that there's a missing collation issue ore something silly like that.
Assuming the db is usable on sql 2000, wouldn't scripting the tables and redeploying on 2k5 (or even 2000), possibly after changing the collation to something else "save the day"?
July 26, 2011 at 1:43 pm
Sqlsavy (7/26/2011)
But I'm still not able to understand how these errors are generated and what is wrong with the data on SQL 2005?
It's metadata errors, there's problems with the data that describes the data
Because everything is alright on SQL 2000
Not necessarily. There were lots of database errors that CheckDB on SQL 2000 just doesn't pick up.
What is the risk of still going ahead with these errors?
Your checkDB will never succeed, that means you'll have a hard time telling if you have more corruption. Queries could generate severe errors, future development could fail, etc, etc.
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
July 26, 2011 at 1:44 pm
Ninja's_RGR'us (7/26/2011)
Assuming the db is usable on sql 2000, wouldn't scripting the table and redeploying on 2k5 (or even 2000), possibly after changing the collation to something else "save the day"?
It's not one table. Look at the object_ids.
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
July 26, 2011 at 1:46 pm
GilaMonster (7/26/2011)
Ninja's_RGR'us (7/26/2011)
Assuming the db is usable on sql 2000, wouldn't scripting the table and redeploying on 2k5 (or even 2000), possibly after changing the collation to something else "save the day"?It's not one table. Look at the object_ids.
Corrected my typo. 😉
July 26, 2011 at 2:38 pm
What does this return on SQL 2000?
SELECT DISTINCT
collation
FROM syscolumns
WHERE id = OBJECT_ID('SALES')
AND collation IS NOT NULL ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 26, 2011 at 2:46 pm
Ninja's_RGR'us (7/26/2011)
Assuming the db is usable on sql 2000, wouldn't scripting the tables and redeploying on 2k5 (or even 2000), possibly after changing the collation to something else "save the day"?
Yes, and that's what I was suggesting with 'script, export and recreate'
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
July 26, 2011 at 2:49 pm
opc.three (7/26/2011)
What does this return on SQL 2000?
SELECT DISTINCTcollation
FROM syscolumns
WHERE id = OBJECT_ID('SALES')
AND collation IS NOT NULL ;
Sales is the name of the database, not a table.
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 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply