DBCC CHECKDB Error Msg 2570, Level 16, State 3

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Data Corruption » DBCC CHECKDB Error Msg 2570, Level 16, State...

DBCC CHECKDB Error Msg 2570, Level 16, State 3

Rate Topic

Display Mode

Topic Options

Author

Message

bsock

Posted Monday, September 27, 2010 8:59 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, September 27, 2010 10:29 AM
Points: 2, Visits: 8

Over the weekend, our database received this error when the DBCC CHECKDB job ran:

DateTime: 2010-09-26 23:15:08
Command: DBCC CHECKDB ([NSpireDB]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY
Msg 2570, Level 16, State 3, Server NSPIRE, Line 1
Page (1:219429), slot 7 in object ID 1445580188, index ID 1, partition ID 72057594204717056, alloc unit ID 72057594208845824 (type "In-row data"). Column "insp_vol" value is out of range for data type "real". Update column to a legal value.
Msg 2570, Level 16, State 3, Server NSPIRE, Line 1
Page (1:219429), slot 7 in object ID 1445580188, index ID 1, partition ID 72057594204717056, alloc unit ID 72057594208845824 (type "In-row data"). Column "sys_dead_space" value is out of range for data type "real". Update column to a legal value.
Msg 2570, Level 16, State 3, Server NSPIRE, Line 1
Page (1:219429), slot 8 in object ID 1445580188, index ID 1, partition ID 72057594204717056, alloc unit ID 72057594208845824 (type "In-row data"). Column "insp_vol" value is out of range for data type "real". Update column to a legal value.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'dl_calc' (object ID 1445580188).
CHECKDB found 0 allocation errors and 3 consistency errors in database 'NSpireDB'.
Outcome: Failed
Duration: 00:03:56
DateTime: 2010-09-26 23:19:04

I've done some research and ran the following command to narrow down the issue:

DBCC TRACEON (3604)
GO
DBCC PAGE (NSpireDB, 1, 219429, 3)
GO

Which printed these results about slot 7 and slot 8:

Slot 7 Column 32 Offset 0xab Length 4
insp_vol = 2.00386e-043

Slot 7 Column 40 Offset 0xcb Length 4
sys_dead_space = 2.00386e-043

Slot 8 Column 32 Offset 0xab Length 4
insp_vol = 2.00386e-043

This data is stored in a table called dl_calc in a column named insp_vol that is of type "real" (length 4, precision 24, scale 0). Can someone help me with what I need to do next to fix the error message? What does that number equal in the results? Do I need to run an update statement against the rows affected? How do I determine what rows to update? Thanks!

Post #993724

GilaMonster

Posted Monday, September 27, 2010 9:52 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 37,731, Visits: 29,994

What you need to do is work out (from DBCC Page) what the values for the primary key are for those rows. Then do an update (using those pk values in the where clause) to set that column to a valid value. Having no idea what that is, I'd probably start with updating to 0, then do further investigation later.

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 #993765

bsock

Posted Monday, September 27, 2010 10:28 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, September 27, 2010 10:29 AM
Points: 2, Visits: 8

Thanks! That worked for me. I knew I was missing something.

Post #993793

GilaMonster

Posted Monday, September 27, 2010 11:56 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 37,731, Visits: 29,994

Glad to hear it.

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 #993878

ALZDBA

Posted Monday, September 27, 2010 12:13 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:17 AM
Points: 6,862, Visits: 8,049

I'm glad it has been solved using the page method.

I've been confronted a couple of times with this issue and I've been able to filter the troublesome rows using this kind of query construct:

select "all columns except the troubled one"
-- into save_data_table
from the_table
where isnumeric(isnull(troubled_column,0)) = 0

Until now, I've been lucky every time only a single column of the row had purity issues.

As Gail stated, you need to investigate the rows and determine its influence at application-data level.

Retain the row and correct the column value if you can, else ... remove the row.

Test it before your delete any rows !!

Off course, after fixing the rows, re-run your data_purity check !

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help

- How to prevent a sore throat after hours of presenting ppt ?

"press F1 for solution", "press shift+F1 for urgent solution" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #993892

ALZDBA

Posted Tuesday, September 28, 2010 2:15 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:17 AM
Points: 6,862, Visits: 8,049

How is this for a coincidence ... just today ... converting another db to sql2005 .... data_purity messages ....
my query only captured 7 rows of the 68 rows reported by dbcc.
( off course I checked the numbers )

So I'll have to recouver the remaining 61 using the page method

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #994238

SQLQuest29

Posted Sunday, October 03, 2010 10:36 AM

Say Hey Kid

Group: General Forum Members
Last Login: Today @ 10:44 AM
Points: 713, Visits: 2,863

bsock (9/27/2010)

Thanks! That worked for me. I knew I was missing something.

Can you describe what did you do to fix the problem ?

Cheers !

HTH,
Cheers !

"Never take life too seriously, nobody gets out of it anyways ! BigGrin

When your love and skills unite, expect a masterpiece !"

Post #997297

Leeland

Posted Monday, July 23, 2012 12:25 PM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, May 20, 2013 12:06 PM
Points: 416, Visits: 1,013

Thanks for this post and thanks Gail for your replies...I ran into this issue today on a SQL 2000 to SQL 2008 R2 upgrade...based on your comments I was able to find the info I needed to get the bad data updated.

Post #1334004

« Prev Topic | Next Topic »

Permissions