DBCC CheckDB Consistency Errors

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

DBCC CheckDB Consistency Errors

Author

Topic

mashedtaz1
Starting Member

9 Posts

Posted - 2011-08-15 : 07:12:25

Hi,

I am new to the dba side of SQL Server but I am the only guy here that has anything to do with the db. I'm actually the data analyst.

I have been learning some more dba oriented tasks and noticed a few issues with our instance. One of those issues is that DBCC CHECKDB has found a couple of consistency errors.

I am struggling to interpret these issues, and it would appear that they have been apparent for a while so I don't think I can restore from a backup to fix.


Failed:(-1073548784) Executing the query "DBCC CHECKDB WITH NO_INFOMSGS
" failed with the following error: "Table error: Object ID 437576597, index ID 1, partition ID 72057596655173632, alloc unit ID 71804796081078272 (type LOB data). The off-row data node at page (1:6311400), slot 23, text ID 2330410942464 is referenced by page (1:6525430), slot 20, but was not seen in the scan.
Object ID 437576597, index ID 1, partition ID 72057596655173632, alloc unit ID 72057596666380288 (type In-row data): Errors found in off-row data with ID 2330410942464 owned by data record identified by RID = (1:6525430:20)
CHECKDB found 0 allocation errors and 2 consistency errors in table 'Bookings' (object ID 437576597).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'LCON_Members'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (LCON_Members).". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Can I run a repair on these errors without losing any data?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-15 : 07:20:46

If restoring a backup isn't an option, you need to take the database into single user mode and run DBCC CheckDB(LCON_Members, repair_allow_data_loss)

There's an error in the LOB data, so you will lose at least one row from the Bookings table.

--
Gail Shaw
SQL Server MVP

mashedtaz1
Starting Member

9 Posts

Posted - 2011-08-15 : 07:23:37

Thanks.

Is there an easy way to identify the offending row?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-15 : 08:24:47

Easy, no, but there is a way.

quote:
Errors found in off-row data with ID 2330410942464 owned by data record identified by RID = (1:6525430:20)

RID is a file, page, slot combination, so DBCC Page for page 1,6525430, Dump style 3 and look for the row with a slot index of 20.
For info on DBCC PAge see http://sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Using-DBCC-PAGE-and-DBCC-IND-to-find-out-if-page-splits-ever-roll-back.aspx

--
Gail Shaw
SQL Server MVP

markwillium
Starting Member

11 Posts

Posted - 2011-08-16 : 05:00:49

If you want to recover whole table without any loss from the table then you can try Stellar sql recovery application.

* Always try demo version of any software before Buy.
Note: Backup is very helpful in any disaster recovery scenarios.

mashedtaz1
Starting Member

9 Posts

Posted - 2011-08-16 : 05:40:14

I identified the row causing the issue. the only way to correct was to repair and lose the row because I did not have a backup that was not corrupt. I will take a look at Stellar SQL Recovery to see if it can repair the missing reference.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-16 : 05:44:24

I wouldn't waste your money...

--
Gail Shaw
SQL Server MVP

mashedtaz1
Starting Member

9 Posts

Posted - 2011-08-16 : 06:16:03

Thanks ;)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-16 : 07:42:23

Sure there's not a really old backup? Not to restore (because too old), but restore as a new DB and see if that row's in it.

--
Gail Shaw
SQL Server MVP

mashedtaz1
Starting Member

9 Posts

Posted - 2011-08-16 : 07:53:24

No unfortunately not. The DR strategy isn't too great around here...

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-16 : 08:21:12

No kidding...

I assume you'll be rectifying that problem now?

--
Gail Shaw
SQL Server MVP

mashedtaz1
Starting Member

9 Posts

Posted - 2011-08-16 : 08:46:22

It's to of my list "implement new DR strategy". They're using simple recovery model for a highly transactional db. Crazyness!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-16 : 10:06:40

I'd suggest to start with full recovery, regular log backups and a regular scheduled CheckDB. That's the basics.

--
Gail Shaw
SQL Server MVP

mashedtaz1
Starting Member

9 Posts

Posted - 2011-08-16 : 10:41:07

Yeah, got it covered. Thanks for the info.

markwillium
Starting Member

11 Posts

Posted - 2011-08-17 : 01:21:40

For me, data is more important than money, anyways I suggest you to try free demo version of "Stellar SQL recovery" and see the preview of your data. Go forward to buy the software if you will be satisfied with the software. It takes some time not money.

Note: It is totally free and shows the preview of data & folder that can be recovered by the software.

elliswhite
Starting Member

36 Posts

Posted - 2014-05-10 : 02:34:28

The main reason behind this error is high level corruption which can not be eliminated by DBCCCheck DB command or any other T-SQL query. If you have a full backup then you can restore from it otherwise try any third party tool.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources