February 20, 2013 at 4:19 am
Hi Team
Last week we run dbcc command on few databases and found the below consistency errors in the database ,no allocation errors .
Please see below error and provide u r inputs .
The error due to decimal data type for column 'amount1' ,
i checked the table and the column is like
amount1 (decimal(13,4),null) ...
************************
DBCC results for 'table1'.
Msg 2570, Level 16, State 3, Line 1
Page (12:3592881), slot 119 in object ID 6447247, index ID 1,
partition ID 72057598160928768, alloc unit ID 72057598189436928 (type
"In-row data"). Column "Amount1" value is out of range for data type
"decimal". Update column to a legal value.
February 20, 2013 at 6:29 am
Basically you have to identify the rows that contain the invalid value and update them with a valid value.
Have a look at:
Troubleshooting DBCC error 2570 in SQL Server 2005 and later versions
http://support.microsoft.com/kb/923247
Look for:
Finding Rows with Invalid Values Using T-SQL Queries
Decimal and Numeric data type:
--
SELECT col1 FROM table2
WHERE col2 > 9999999999.99999
OR col1 < -9999999999.99999
--
Keep in mind that you will need to adjust the values based on the precision and scale with which you have defined the decimal or numeric column. In the above example, the column was defined as col2 decimal(15,5).
February 20, 2013 at 6:55 am
what is col1 in this script??
February 20, 2013 at 7:17 am
Primary key maybe.
Something that helps you identify the row.
You could also narrow down the problem if above isn't working for you.
Select column from xx
where column between 1 and 100.
if it doesnt fail continue to between 100 and 1000 until you narrow it down.
In the end you will find the row causing the problem and then run an update
statment on that row that fixes the out of range value.
February 20, 2013 at 7:25 am
Take a look at this article. http://www.sqlservercentral.com/articles/65804/, there's a section on data purity errors and a like to a kb article that goes into detail on fixing them
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
February 20, 2013 at 7:56 am
Same link to the kb that I posted above.
February 20, 2013 at 8:44 am
Let's make that link an actual link:
http://support.microsoft.com/kb/923247
The article Gail linked to above has a more "to the point" explanation of why these values can exist in your database, just search for the section named "Data Purity errors."
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 21, 2013 at 2:29 am
Thanks to all.
I go through the link , now my issue with the decimal data type
SELECT col1 FROM table2
WHERE col2 > 9999999999.99999
OR col1 < -9999999999.99999
In this script col1 is primary key and col2 is the column from eroor.
The same thing i checked for my issue,some tables doesn't have any column with primary key, and some tables have clumn primary key but its datatype is int not decimal .
suggest me how to get data_purity values using the above script ..
Col1 and col2 should be same data type (decimal)??
Lavanyasri
February 21, 2013 at 2:43 am
Lavanyasri (2/21/2013)
Thanks to all.I go through the link , now my issue with the decimal data type
SELECT col1 FROM table2
WHERE col2 > 9999999999.99999
OR col1 < -9999999999.99999
In this script col1 is primary key and col2 is the column from eroor.
The same thing i checked for my issue,some tables doesn't have any column with primary key, and some tables have clumn primary key but its datatype is int not decimal .
suggest me how to get data_purity values using the above script ..
Col1 and col2 should be same data type (decimal)??
Lavanyasri
The above code looks like a typo error
what you need to check is
SELECT col1 FROM table2
WHERE col2 > 9999999999.99999
OR col2 < -9999999999.99999
Here Col1 is the primary key and col2 is the decimel you need to check.
Col1(PK) doesnt need to be of the same datatype. You need this column to uniquely idenntify the corrupt rows,
decide on what to do with them
February 22, 2013 at 1:02 am
some tables doesn't have any column with primary key....
if i run that script on production, it will cause any performance issues ??
February 22, 2013 at 7:08 am
The query will scan every row in the table unless the column happens to be the leading column in an index. Scanning large tables can impact performance. You may want to restore a backup of the database to a non-production location and do your research there. It would be advisable to test the fixes there as well, then run CHECKDB to ensure you have resolved the data issue, and then run those fixes in production.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply