December 7, 2011 at 6:34 pm
Hi-- Thanks in advance for the help!
I'm getting integrity issues after I migrate a DB from SQL Server 2000 to SQL Server 2008 R2.
When I run DBCC CHECKDB on the original 2K db, I get no errors. Then I tried DBCC CHECKCATALOG and got a bunch of objects that do "not match between 'SYSREFERENCES' and 'SYSINDEXES'."
I'm not sure how far the backups go. Assuming I don't have any non-corrupt backups, what are my options here? I've read that it's easier to fix the issues on 2k?
Thanks!
2KR2 DBCC CHECKDB outpout:
Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=27147142,key_index_id=2) of row (object_id=1052582838) in sys.foreign_keys does not have a matching row (object_id=27147142,index_id=2) in sys.indexes. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=748581755,key_index_id=2) of row (object_id=828582040) in sys.foreign_keys does not have a matching row (object_id=748581755,index_id=2) in sys.indexes. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=748581755,key_index_id=2) of row (object_id=844582097) in sys.foreign_keys does not have a matching row (object_id=748581755,index_id=2) in sys.indexes. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=914154352,key_index_id=2) of row (object_id=506133244) in sys.foreign_keys does not have a matching row (object_id=914154352,index_id=2) in sys.indexes. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=914154352,key_index_id=2) of row (object_id=522133301) in sys.foreign_keys does not have a matching row (object_id=914154352,index_id=2) in sys.indexes. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=914154352,key_index_id=2) of row (object_id=1010154694) in sys.foreign_keys does not have a matching row (object_id=914154352,index_id=2) in sys.indexes. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=914154352,key_index_id=2) of row (object_id=1042154808) in sys.foreign_keys does not have a matching row (object_id=914154352,index_id=2) in sys.indexes. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=914154352,key_index_id=2) of row (object_id=1723921263) in sys.foreign_keys does not have a matching row (object_id=914154352,index_id=2) in sys.indexes. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=964914509,key_index_id=2) of row (object_id=686989874) in sys.foreign_keys does not have a matching row (object_id=964914509,index_id=2) in sys.indexes. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=1778821399,key_index_id=2) of row (object_id=1084582952) in sys.foreign_keys does not have a matching row (object_id=1778821399,index_id=2) in sys.indexes. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=1904061869,key_index_id=2) of row (object_id=1936061983) in sys.foreign_keys does not have a matching row (object_id=1904061869,index_id=2) in sys.indexes. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=2092586543,key_index_id=2) of row (object_id=73103351) in sys.foreign_keys does not have a matching row (object_id=2092586543,index_id=2) in sys.indexes. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=2092586543,key_index_id=2) of row (object_id=1739921320) in sys.foreign_keys does not have a matching row (object_id=2092586543,index_id=2) in sys.indexes. CHECKDB found 0 allocation errors and 13 consistency errors not associated with any single object. Msg 2508, Level 16, State 3, Line 1 The In-row data RSVD page count for object "tblUnit_bak2", index ID 0, partition ID 32571823816704, alloc unit ID 32571823816704 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. CHECKDB found 0 allocation errors and 1 consistency errors in table 'tblUnit_bak2' (object ID 497006589). CHECKDB found 0 allocation errors and 14 consistency errors in database 'XXXXXX'.
December 7, 2011 at 6:49 pm
Looks like someone played with the system tables to delete Fks or something evil like that.
If it's the case your backups will most likely be useless since the corruption will be very old.
IIRC there's a manual fix for this but it's not pretty and I'll let our real expert guide you on this one (Gail).
December 7, 2011 at 6:57 pm
If this database is small and merely complex, I have a recommendation...
Script out all the schema, drop it to a new DB shell, and port your data over. Grab all the secondary structures (procs, functions, etc). Back THAT up and port that up to 2K8.
I don't know of an easy fix to this besides basically working off a new shell. If there is one I'm sure someone will recommend it, but this is what I had to do when I ran into that issue once. I couldn't find anything then and a brief poke around the web wasn't very forthcoming.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 8, 2011 at 2:53 am
The reason that CheckDB on SQL 2000 came back clean is that SQL 2000 CheckDB doesn't run CheckCatalog. SQL 2008 does.
Do you have a backup/copy from SQL 2000? From before you upgraded? Can you go back to that, fix and reupgrade?
If so, the fix is easy(ish). If not, then the fixes are incredibly difficult.
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
December 8, 2011 at 10:50 am
Thank you for your responses. I found a back up from 10/08 and the errors are still there, so the problem's been around for a while.
In any case, the application is still using the 2000 db-- I haven't migrated yet. So, hopefully, I can implement the easy(ish) fix you're referring to, Gail. Can you point me in the right direction?
-J
December 8, 2011 at 11:16 am
There isn't an article that I know of...
Can you run DBCC CheckCatalog on the SQL 2000 database and post the full and complete results.
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
December 8, 2011 at 11:30 am
Here are the results... (Why would there be fewer errors here than in the 2008 results pasted above? )
Server: Msg 2513, Level 16, State 14, Line 1
Table error: Object ID 2092586543 (object 'tblCompGroup') does not match between 'SYSREFERENCES' and 'SYSINDEXES'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 964914509 (object 'tblValidationSeverity') does not match between 'SYSREFERENCES' and 'SYSINDEXES'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 27147142 (object 'tblQuestionType') does not match between 'SYSREFERENCES' and 'SYSINDEXES'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 1778821399 (object 'tblHelp') does not match between 'SYSREFERENCES' and 'SYSINDEXES'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 2092586543 (object 'tblCompGroup') does not match between 'SYSREFERENCES' and 'SYSINDEXES'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 1904061869 (object 'tblSecurityObject') does not match between 'SYSREFERENCES' and 'SYSINDEXES'.
DBCC results for 'current database'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
December 8, 2011 at 11:31 am
CHECKDB was extremelly enhanced between 2000 and 2005. There's a joke going around that this was the reason for the 5 years between releases.
December 8, 2011 at 11:50 am
jacobbdrew (12/8/2011)
Here are the results... (Why would there be fewer errors here than in the 2008 results pasted above? )
Because the system tables were completely changed between the versions, and checkDB on 2005 is vastly improved over SQL 2000.
Ok, query sysreferences and sysindexes for the rows for the listed objectids and post what you get back
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
December 8, 2011 at 12:17 pm
OK, I'm not sure this is right...
I ran:
select constid, fkeyid, rkeyid, rkeyindid, keycnt, forkeys, refkeys, fkey1
from sysreferences where rkeyid
in (2092586543,964914509,27147142,1778821399,2092586543,1904061869)
(do you need other columns? when i selected * and pasted results, it was pretty unreadable. also, rkeyid is the correct field to filter on?)
results:
105258283895658249627147142210x03000x01003
6869898741274487619964914509210x07000x01007
10845829529565824961778821399210x0B000x010011
193606198317169171881904061869210x04000x01004
7310335110935789342092586543210x01000x01001
173992132016919211492092586543210x02000x01002
as for the sysindexes, do you want to see all the columns?
December 8, 2011 at 12:46 pm
Could you stick the sysreferences and sysindexes outputs into an excel spreadsheet, along with column headers please?
Honestly not sure which column, should be id, but check Books Online. I haven't worked with the 2000 tables in quite a few years.
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
December 8, 2011 at 1:39 pm
outputs in excel. thanks for taking the time to help me with this...
-J
December 8, 2011 at 3:14 pm
As a side note, the reason I noticed the problem in the first place was because a simple insert was failing on the 2008 DB.
I run this on 2008
INSERT INTO tblMessage( userid, messageText, targetid, subject, enddate, startdate, email, clientid, listid, forAllUsers)
VALUES( 13653, 'test', 0, 'TEST', '12/7/2011', '12/7/2011', 1, 5, 939, 0)
and get:
Location: idxutil.cpp:5867
Expression: ipidx < m_cidx
SPID: 51
Process ID: 3456
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
HOWEVER
I run the same insert on the 2000 DB and it works fine, no errors.
Any ideas why this would be?
December 9, 2011 at 11:52 am
Hi Gail-- any more thoughts on this? what's the general idea behind the fix you're thinking of? thanks
December 9, 2011 at 2:15 pm
Sorry, no time today to sit and work on this. It's not a quick thing to work out.
We have to delete the orphaned rows, delete the wrong ones and stuff breaks, bad.
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 27 total)
You must be logged in to reply to this topic. Login to reply