September 27, 2010 at 8:59 am
Over the weekend, our database received this error when the DBCC CHECKDB job ran:
[font="Courier New"]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[/font]
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:
[font="Courier New"]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[/font]
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!
September 27, 2010 at 9:52 am
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
September 27, 2010 at 10:28 am
Thanks! That worked for me. I knew I was missing something.
September 27, 2010 at 11:56 am
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
September 27, 2010 at 12:13 pm
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
Learn to play, play to learn !
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[/url]
- 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
September 28, 2010 at 2:15 am
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
Learn to play, play to learn !
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[/url]
- 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
October 3, 2010 at 10:36 am
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 🙂
July 23, 2012 at 12:25 pm
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply