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»»

DBCC Checkdb Problems Expand / Collapse
Author
Message
Posted Thursday, January 17, 2013 2:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 7:50 AM
Points: 63, Visits: 354
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
Post #1408250
Posted Thursday, January 17, 2013 3:03 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: Yesterday @ 8:31 AM
Points: 40,456, Visits: 36,912
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 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 #1408255
Posted Thursday, January 17, 2013 3:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 7:50 AM
Points: 63, Visits: 354
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??
Post #1408282
Posted Thursday, January 17, 2013 3:35 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: Yesterday @ 8:31 AM
Points: 40,456, Visits: 36,912
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 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 #1408286
Posted Thursday, January 17, 2013 4:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:11 AM
Points: 2,840, Visits: 3,976
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
Post #1408320
Posted Thursday, January 17, 2013 5:03 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: Yesterday @ 8:31 AM
Points: 40,456, Visits: 36,912
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 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 #1408345
Posted Thursday, January 17, 2013 5:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 7:50 AM
Points: 63, Visits: 354
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?
Post #1408349
Posted Thursday, January 17, 2013 5:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:11 AM
Points: 2,840, Visits: 3,976
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
Post #1408353
Posted Thursday, January 17, 2013 6:38 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: Yesterday @ 8:31 AM
Points: 40,456, Visits: 36,912
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 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 #1408395
Posted Thursday, January 17, 2013 6:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 4:10 AM
Points: 5,221, Visits: 5,119
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', 1
RECONFIGURE

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?




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1408397
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse