DBCC Checkdb Problems

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » DBCC Checkdb Problems

12 posts, Page 1 of 2

1 2 »»

DBCC Checkdb Problems

Rate Topic

Display Mode

Topic Options

Author

Message

mitzyturbo

Posted Thursday, January 17, 2013 2:50 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 2:14 AM
Points: 45, Visits: 206

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 Sad

-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

GilaMonster

Posted Thursday, January 17, 2013 3:03 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:02 AM
Points: 37,677, Visits: 29,932

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

mitzyturbo

Posted Thursday, January 17, 2013 3:28 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 2:14 AM
Points: 45, Visits: 206

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

GilaMonster

Posted Thursday, January 17, 2013 3:35 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:02 AM
Points: 37,677, Visits: 29,932

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? BigGrin

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

Bhuvnesh

Posted Thursday, January 17, 2013 4:18 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

GilaMonster (1/17/2013)

What's powershell? BigGrin

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1408320

GilaMonster

Posted Thursday, January 17, 2013 5:03 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:02 AM
Points: 37,677, Visits: 29,932

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

mitzyturbo

Posted Thursday, January 17, 2013 5:06 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 2:14 AM
Points: 45, Visits: 206

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

Bhuvnesh

Posted Thursday, January 17, 2013 5:14 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1408353

GilaMonster

Posted Thursday, January 17, 2013 6:38 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:02 AM
Points: 37,677, Visits: 29,932

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

anthony.green

Posted Thursday, January 17, 2013 6:40 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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?

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 »

12 posts, Page 1 of 2

1 2 »»

Permissions