DBCC CheckDB

Question

DBCC CheckDB

Simon_L

SSCarpal Tunnel

Points: 4669
More actions
September 5, 2008 at 7:47 am

#192425

Hi
Got a few confusing points with DBCC CHECKDB that I could do with some advice/help with.
I run a dbcc checkdb on the db
CHECKDB found 0 allocation errors and 114 consistency errors in database 'MyDatabase'
(btw consistency errors always refer to indexes I thought but the column effected has none ?)
anyway from these results I then run
dbcc checktable ('company')
And get
Server: Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 1797581442, index ID 0, page ID (1:13904), row ID 1. Column 'IDCHECK' was created NOT NULL, but is NULL in the row.
DBCC results for 'COMPANY'.
So check the table , its a bit , not nullable default 0. Return the two rows in the table and both have 0 in there .
So am I missing the point somewhat ??
help !
~si

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I believe that INDEX ID 0 means the table is in a heap, no clustered index and is now in an inconsistent state. I would restore a backup to a test server and create a clustered index on the table and then run checktable on it to see if that fixes the issue. You can always drop the clustered index after as well, although I believe every table should have a clustered index.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 2

thank you jack .. so why does the column mentioned in the results have no relation to the actual column in the tables clustered index ?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

If there is a clustered index on the table then DBCC CheckTable should not return an error for indexid 0, so I don't think there is a clustered index on the table. I think that creating a clustered index will re-organize the table on disk and fix the consistency error. I can't guarantee it, but I think it's worth a try.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 4

yeah the table does have a clustered index on one column and a compound PK on two columns 🙁

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

What do you get when you run:

Select * from sysindexes where object_id = Object_Id("table name")

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 6

Select NAME from sysindexes where ID = Object_Id('company')

gives

ICX_COMPANY (clustered)

PK_COMPANY (nonclustered)

and about 98 _WA_SYS temp thingies

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

You need the indid in the query as well.

I would still restore a backup to a test server and drop and recreate the clustered index to see if that cleans the error. Otherwise it's a Repair at whatever level the DBCC statement recommends.

I'm really confused by the Index ID 0 as that is only available for heaps as the clustered index is 1 and other indexes are > 1.

The _WA stuff is column stats I believe.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 8

sorry my bad ,, ok with indid

ICX_COMPANY 1

PK_COMPANY 2

restored db to test server , dropped and recreated clustered index .. ran checktable .. still getting

Server: Msg 8970, Level 16, State 1, Line 1

Row error: Object ID 1797581442, index ID 0, page ID (1:1863), row ID 1. Column 'CHECK_STUFFING' was created NOT NULL, but is NULL in the row.

DBCC results for 'COMPANY'.

There are 2 rows in 1 pages for object 'COMPANY'.

CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'COMPANY' (object ID 1797581442).

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (iSBSEmployeeRoseland600.dbo.COMPANY ).

Jack Corbett SSC Guru Points: 184394 More actions · Answer 9

How about running an update against that column setting the value correctly. I know it's okay now, but running the update can't hurt.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

I'm not sure I'm following here Jack, can you explain a little bit more?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 11

I'm just grasping at straws. Odds are the only solution is to run DBCC CHECKTABLE() with REPAIR_ALLOW_DATA_LOSS or restoring from a good backup. It just seems odd to me that it returns an Index ID of 0 on a table with a clustered index. If you get a consistency error in an index you can drop and recreate the index to fix the consistency error, but since this is not a consistency error in an index apparently, I thought fixing the data might work. Basically I am assuming there is no good backup, at least not recently enough, and that we want to avoid the repair so I am listing things I'd try.

I'm open to suggestions.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Ninja's_RGR'us

SSC Guru

Points: 294069

September 5, 2008 at 12:28 pm

#867989

Ah, now that makes sens!

Thanks 😀

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 13

Im so confused now !! ok.. the original error unless Im mistaken referred to index problems ? even though it referred to a column that is not covered by an index... maybe my understanding of dbcc checktable is flawed...

anyways following Jacks suggestion I ran an update over the column merely replacing the default values of 0 with a 0.. and then sure enough the dbcc check worked ?!

So thank you Jack ... I just wish I understood the how and the why and the wherefore etc

~si

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 14

also I feel my lack of understanding as to what does constitute a consistency errors i,e

CHECKDB found 0 allocation errors and 114 consistency errors in database

Is that a physical schema corruption cause by IO .. any index problem