SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DBCC CHECKDB Error Msg 2570, Level 16, State 3


DBCC CHECKDB Error Msg 2570, Level 16, State 3

Author
Message
bsock
bsock
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 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!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226837 Visits: 46335
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, MVP, M.Sc (Comp Sci)
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


bsock
bsock
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 8
Thanks! That worked for me. I knew I was missing something.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226837 Visits: 46335
Glad to hear it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


ALZDBA
ALZDBA
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30143 Visits: 8986
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


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont 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 :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
ALZDBA
ALZDBA
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30143 Visits: 8986
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 Rolleyes )


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

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont 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 :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
SQLQuest29
SQLQuest29
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2199 Visits: 4368
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 :-)
Leeland
Leeland
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2106 Visits: 1331
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search