January 17, 2013 at 2:50 am
Hi guys
Hoping that someone can point me in the right direction on this
Running DBCC CheckDB against a database, returns the following error:
Failed:(-1073548784) Executing the query "DBCC CHECKDB(N'XXX') WITH NO_INFOMSGS
" failed with the following error: "Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=1) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.
Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=2) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.
Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=3) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.
Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=4) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.
Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=5) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.
Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=6) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.
Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=7) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.
Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=8) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.
Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=9) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.
Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=10) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.
Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=11) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.
Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=12) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.
Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=13) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.
Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=14) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.
Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=15) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.
Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=16) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.
Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=17) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.
Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=18) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.
Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=19) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.
Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=20) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.
Check Catalog Msg 3853, State 1: Attribute (object_id=528161077) of row (object_id=528161077,column_id=21) in sys.columns does not have a matching row (object_id=528161077) in sys.objects.
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:75945) in object ID 0, index ID -1, partition ID 0, alloc unit ID 316088541052928 (type Unknown), but it was not detected in the scan.
CHECKDB found 1 allocation errors and 21 consistency errors not associated with any single object.
CHECKDB found 1 allocation errors and 21 consistency errors in database 'XXXX'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
A bit of background on this, the database in question was recently migrated from a sql 2000 server to sql 2008 R2.My own guess on this is that someone on the 2000 database server altered something on sys.indexes which has caused this.
Some of the checks we’ve done:
--identified the names of the columns in question
select * from syscolumns where id = 528161077
We could see 1 table that has 20 of the 21 columns, the odd column out was used only in views. We tried dropping these views and the table we identified and ran the dbcc checkdb command again and got the same result
Ran select * from syscolumns where id = 528161077 again still got 21 results (thought that strange since we had dropped the views where the odd column was used)
--identified all the tables where this object_id is used in sys.columns
SELECT distinct c.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
sys.objects o
on o.name = c.table_name
where o.type = 'U'
and c.column_name in
(select name from sys.columns
where object_id = 528161077)
Query returns 32 tables
--Ran the following
dbcc traceon (3604, -1)
go
dbcc page ('XXXX',1,75945,3)
go
Got the following results
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PAGE: (1:75945)
BUFFER:
BUF @0x000000034AFC5200
bpage = 0x000000034A630000 bhash = 0x0000000000000000 bpageno = (1:75945)
bdbid = 23 breferences = 3 bcputicks = 0
bsampleCount = 0 bUse1 = 5041 bstat = 0xc00009
blog = 0x21212159 bnext = 0x0000000000000000
PAGE HEADER:
Page @0x000000034A630000
m_pageId = (1:75945) m_headerVersion = 1 m_type = 10
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 528161077 m_indexId (AllocUnitId.idInd) = 0
Metadata: AllocUnitId = 34613564342272 Metadata: PartitionId = 0
Metadata: IndexId = -1 Metadata: ObjectId = 0 m_prevPage = (0:0)
m_nextPage = (0:0) pminlen = 90 m_slotCnt = 2
m_freeCnt = 6 m_freeData = 8182 m_reservedCnt = 0
m_lsn = (441326:400:53) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 1
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:72792) = 0x70 IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED
IAM: Header @0x000000004DACA064 Slot 0, Offset 96
sequenceNumber = 0 status = 0x0 objectId = 0
indexId = 0 page_count = 0 start_pg = (1:0)
IAM: Single Page Allocations @0x000000004DACA08E
Slot 0 = (1:75944) Slot 1 = (1:75946) Slot 2 = (1:75947)
Slot 3 = (1:75948) Slot 4 = (1:75949) Slot 5 = (1:75950)
Slot 6 = (1:75951) Slot 7 = (1:75952)
IAM: Extent Alloc Status Slot 1 @0x000000004DACA0C2
(1:0) - (1:75952) = NOT ALLOCATED
(1:75960) - (1:76736) = ALLOCATED
(1:76744) - (1:511224) = NOT ALLOCATED
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
If anyone can point me in the right direction here it would be greatly appreciated
January 17, 2013 at 3:03 am
This one's nasty.
Someone, at some time, made changes directly to the system tables in SQL 2000. SQL 2000 didn't check for that when it ran checkDB, so it went unnoticed. SQL 2008 does, but it's incredibly hard to fix.
Two options:
1) Script all objects, export all data, recreate the database.
2) Hack the system tables in SQL 2008 (no, sys.tables, sys.objects and sys.columns are not the tables). The system tables are completely undocumented, if you make a mistake you could make things worse, and making the changes will leave a permanent record in the database that someone fiddled with the system tables. CSS may refuse to help you if you have problems in the future.
Given that you also have an IAM error (same kind of cause), I'd go for option 1 if it were my database or a client's 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
January 17, 2013 at 3:28 am
Cheers Gilla, have read a few blogs and articles about it and it would appear that if all else fails, recreate the database and import the data back across.
Don't suppose you have any links to threads about doing this in a nice powershell script??
January 17, 2013 at 3:35 am
mitzyturbo (1/17/2013)
Cheers Gilla, have read a few blogs and articles about it and it would appear that if all else fails, recreate the database and import the data back across.
That's what I'd recommend. There's no automated fix for this, checkDB can't repair it. Dropping objects is not going to fix it.
Don't suppose you have any links to threads about doing this in a nice powershell script??
What's powershell? 😀
Sorry, that's not something I use much, so don't know very well.
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
January 17, 2013 at 4:18 am
GilaMonster (1/17/2013)
What's powershell? 😀Sorry, that's not something I use much, so don't know very well.
The specialist is a person who knows everything about something but nothing about anything else.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 17, 2013 at 5:03 am
Bhuvnesh (1/17/2013)
The specialist is a person who knows everything about something but nothing about anything else.
If you want to be insulting, kindly be so elsewhere.
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
January 17, 2013 at 5:06 am
We still have the 2000 instance of this db, is there anyway we can pinpoint the change at that level, identify and migrate the fix?
January 17, 2013 at 5:14 am
GilaMonster (1/17/2013)
Bhuvnesh (1/17/2013)
The specialist is a person who knows everything about something but nothing about anything else.If you want to be insulting, kindly be so elsewhere.
Absolutely NOT gail. i respect you and you know that . actually this was the complement generally my last cmpany's CEO used to give. apology if it hurts you.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 17, 2013 at 6:38 am
mitzyturbo (1/17/2013)
We still have the 2000 instance of this db, is there anyway we can pinpoint the change at that level, identify and migrate the fix?
Oh yes, absolutely and very easy in SQL 2000. If you can fix there and re-upgrade the DB, that's by far the easiest.
SELECT * FROM syscolumns WHERE objectid = 528161077
That should return 21 rows. If so...
EXEC sp_configure 'allow updates', 1
RECONFIGURE
DELETE FROM syscolumns WHERE objectid = 528161077
GO
EXEC sp_configure 'allow updates', 0
RECONFIGURE
GO
DBCC CheckCatalog (<database name>)
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
January 17, 2013 at 6:40 am
GilaMonster (1/17/2013)
mitzyturbo (1/17/2013)
We still have the 2000 instance of this db, is there anyway we can pinpoint the change at that level, identify and migrate the fix?Oh yes, absolutely and very easy in SQL 2000. If you can fix there and re-upgrade the DB, that's by far the easiest.
SELECT * FROM syscolumns WHERE objectid = 528161077
That should return 21 rows. If so...
EXEC sp_configure 'allow updates', 1RECONFIGURE
DELETE FROM syscolumns WHERE objectid = 528161077
GO
EXEC sp_configure 'allow updates', 1
RECONFIGURE
GO
DBCC CheckCatalog (<database name>)
I am guessing that the second sp_configure should be to set it back to 0 to prevent updates?
January 17, 2013 at 6:46 am
Ack. Fixed.
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
January 18, 2013 at 5:08 am
Thanks for all the help on this Gail
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply