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 12»»

DBCC consistency error Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2013 4:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:01 AM
Points: 208, Visits: 776
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.

Post #1422021
Posted Wednesday, February 20, 2013 6:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 3:49 AM
Points: 51, Visits: 427
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).
Post #1422054
Posted Wednesday, February 20, 2013 6:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:01 AM
Points: 208, Visits: 776
what is col1 in this script??
Post #1422061
Posted Wednesday, February 20, 2013 7:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 3:49 AM
Points: 51, Visits: 427
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.
Post #1422073
Posted Wednesday, February 20, 2013 7:25 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 @ 9:13 AM
Points: 40,609, Visits: 37,070
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 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 #1422080
Posted Wednesday, February 20, 2013 7:56 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 3:49 AM
Points: 51, Visits: 427
Same link to the kb that I posted above.
Post #1422097
Posted Wednesday, February 20, 2013 8:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:51 PM
Points: 7,140, Visits: 12,763
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
Post #1422124
Posted Thursday, February 21, 2013 2:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:01 AM
Points: 208, Visits: 776
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


Post #1422454
Posted Thursday, February 21, 2013 2:43 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, December 12, 2014 12:19 AM
Points: 647, Visits: 1,311
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





-- Roshan Joe
*******************************************
There are two types of DBAs. Those who has skills and those who have permissions
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups
Post #1422460
Posted Friday, February 22, 2013 1:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:01 AM
Points: 208, Visits: 776
some tables doesn't have any column with primary key....
if i run that script on production, it will cause any performance issues ??


Post #1422899
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse