InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 09:36:34 GMT20http://www.sqlservercentral.com/Forums/Topic993724-266-1.aspxThanks 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.Mon, 23 Jul 2012 12:25:34 GMTLeelandhttp://www.sqlservercentral.com/Forums/Topic993724-266-1.aspx[quote][b]bsock (9/27/2010)[/b][hr]Thanks! That worked for me. I knew I was missing something.[/quote]Can you describe what did you do to fix the problem ?Cheers !Sun, 03 Oct 2010 10:36:37 GMTSQLQuest29http://www.sqlservercentral.com/Forums/Topic993724-266-1.aspxHow is this for a coincidence ... just today ... converting another db to sql2005 .... data_purity messages .... [b]my query only captured 7 rows of the 68 rows reported by dbcc.( off course I checked the numbers :rolleyes: )[/b]So I'll have to recouver the remaining 61 using the page methodTue, 28 Sep 2010 02:15:08 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic993724-266-1.aspxI'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:[i]select "all columns except the troubled one"-- into save_data_tablefrom the_table[b]where isnumeric(isnull(troubled_column,0)) = 0[/b][/i]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. [b]Retain the row and correct the column value if you can,[/b] else ... remove the row.[u]Test it before your delete any rows !![/u]Off course, after fixing the rows, re-run your data_purity check !Mon, 27 Sep 2010 12:13:37 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic993724-266-1.aspxGlad to hear it.Mon, 27 Sep 2010 11:56:53 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic993724-266-1.aspxThanks! That worked for me. I knew I was missing something.Mon, 27 Sep 2010 10:28:23 GMTbsockhttp://www.sqlservercentral.com/Forums/Topic993724-266-1.aspxWhat 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.Mon, 27 Sep 2010 09:52:58 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic993724-266-1.aspx[b] Over the weekend, our database received this error when the DBCC CHECKDB job ran: [/b][font="Courier New"]DateTime: 2010-09-26 23:15:08Command: DBCC CHECKDB ([NSpireDB]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITYMsg 2570, Level 16, State 3, Server NSPIRE, Line 1Page (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 1Page (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 1Page (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: FailedDuration: 00:03:56DateTime: 2010-09-26 23:19:04[/font][b] I've done some research and ran the following command to narrow down the issue: [/b][code="sql"]DBCC TRACEON (3604)GODBCC PAGE (NSpireDB, 1, 219429, 3)GO [/code][b] Which printed these results about slot 7 and slot 8: [/b][font="Courier New"]Slot 7 Column 32 Offset 0xab Length 4insp_vol = 2.00386e-043 Slot 7 Column 40 Offset 0xcb Length 4sys_dead_space = 2.00386e-043 Slot 8 Column 32 Offset 0xab Length 4insp_vol = 2.00386e-043[/font][b] This data is stored in a table called [i] dl_calc [/i] in a column named [i]insp_vol[/i] that is of type [i]"real" (length 4, precision 24, scale 0)[/i]. 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! [/b]Mon, 27 Sep 2010 08:59:32 GMTbsock