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

DBCC CHECKDB Error Msg 2570, Level 16, State 3 Expand / Collapse
Author
Message
Posted Monday, September 27, 2010 8:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, September 27, 2010 9:52 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: Today @ 11:10 AM
Points: 40,176, Visits: 36,577
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
Posted Monday, September 27, 2010 10:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, September 27, 2010 11:56 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: Today @ 11:10 AM
Points: 40,176, Visits: 36,577
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
Posted Monday, September 27, 2010 12:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 10:42 AM
Points: 6,731, Visits: 8,480
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


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

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


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #993892
Posted Tuesday, September 28, 2010 2:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 10:42 AM
Points: 6,731, Visits: 8,480
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


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

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


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #994238
Posted Sunday, October 3, 2010 10:36 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 737, Visits: 3,777
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 !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor
Post #997297
Posted Monday, July 23, 2012 12:25 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:38 PM
Points: 417, Visits: 1,104
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 »

Add to briefcase

Permissions Expand / Collapse