March 12, 2010 at 6:53 am
After doing a restore to migrate a SQL 2000 database to SQL 2008 we ran the DBCC CHECKDB DATA_PURITY command which identified Invalid Rows in several tables.
The data type of many of the identified columns was numeric(16,2), and we have been attempting to follow the directions for locating invalid rows with T-SQL as detailed in http://support.microsoft.com/default.aspx/kb/923247?p=1. So for a numeric(16,2), we used the following T-SQL statement:
select * FROM OPPORTUNITY_FACT
where RECURRING_REVENUE_AMOUNT > 99999999999999.99 -- for numeric(16,2)
or RECURRING_REVENUE_AMOUNT < -99999999999999.99
This, however does not return any rows. We can however, locate the row and the offending column with the DBCC PAGE command, but in order to correct the values, we need to be able to reference the rows in T-SQL. In addition to the query above, we have tried locating rows with invalid columns in them with a where clause referencing other (valid) fields (and values) in the row. No luck.
Any ideas? Thanks.
UPDATE: Sorry I didn't include the output. I am attaching it now...
March 12, 2010 at 7:13 am
Post the actual dbcc output !
Can you drop and re-create all indexes of that / those tables ?
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
March 12, 2010 at 7:35 am
ALZDBA - Can you explain what dropping adn recreating the indexes will do, apart from waste time and disk space?
djrgreene - you need to have a value that you can do the delete by. Does the table have a clustered index, or any other column that's unique in each row? You can get the value of that column from DBCC PAGE and use that in the delete statement.
If it's not confidential, can you post the DBCC PAGE output for the broken row?
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
March 12, 2010 at 7:55 am
Paul Randal (3/12/2010)
ALZDBA - Can you explain what dropping adn recreating the indexes will do, apart from waste time and disk space?
since we don't know the actual dbcc output .....
If the error is index related ..... it would make sence.
And I would prefer doing it that way, compared to the notepad solution.
The only ref I have used (sql2005) besides the KB is http://www.sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-How-to-tell-if-data-purity-checks-will-be-run.aspx :w00t:
and http://www.mssqltips.com/tip.asp?tip=1119
However ... OP states SQL2008 ...
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
March 12, 2010 at 8:05 am
And yet you recommended a solution with no idea what the problem is?
He(?) clearly stated its a data purity error - which finds invalid data for columns with data types with valid value boundaries in table rows. The KB article explains this and also what to do. Rebuilding an index will never solve this problem. Please don't offer advice on corruption issues unless you know it is 100% correct. Speculative advice at best wastes time and at worst makes things worse for the poster.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
March 12, 2010 at 12:01 pm
Let's see what full error info OP provides and work from there on.
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
March 12, 2010 at 12:19 pm
Dude - they're 2570 data purity errors. Rebuilding indexes is **NEVER** a solution for fixing corruption. You're wrong. Move on.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
March 12, 2010 at 12:31 pm
I could really go for some popcorn while I watch the show.
March 12, 2010 at 12:47 pm
While not a corruption ninja, I will say that I'd agree with Paul on this one. I do notice that the query you posted does NOT match the one in the KB article because you don't have a negative value. Your query should be this:
select * FROM OPPORTUNITY_FACT
where RECURRING_REVENUE_AMOUNT > 99999999999999.99 -- for numeric(16,2)
or RECURRING_REVENUE_AMOUNT < -99999999999999.99
which may explain why you weren't getting any rows.
As Paul says, this is not an index issue, so re-creating indexes won't fix the problem because the data is wrong.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 12, 2010 at 3:14 pm
Hey, hey, I don't argue about being wrong or not.
I can live with the fact I'm only human.
I've only received way to much calls stating heaven did fall down, only to discover later on a shower has passed.
Until we have the actual error/errorlog data ... (as requested)
Also I wonder what a normal checkdb provides at the sql2000 instance of this db.
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
March 12, 2010 at 3:48 pm
A 2000 CHECKDB won't show problems because I didn't add all the data purity code until 2005.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
March 12, 2010 at 6:43 pm
Paul Randal (3/12/2010)
Rebuilding indexes is **NEVER** a solution for fixing corruption.
:blink: :unsure:
Unless the a corruption is confined to the non-clustered index, right?
Not the case here - for sure - but all caps and asterisks deserve some qualification. 😉
I am sure ALZDBA was just trying to help.
I have never come across a DATA_PURITY error myself, so I am guessing - but if the table is relatively small and circumstances allow, I would be tempted to bcp the data out in a human-readable format, correct it, drop and re-create the offending table, and copy the data back in. No doubt there is a better way to do this...?
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 12, 2010 at 7:12 pm
We're talking about table corruption here. The real crux of the matter was 'rebuilding indexes' being offered as a solution without demonstrating understanding of the problem. All caps and asterisks are called for when someone continues to argue something that is wrong.
Rebuilding a corrupt nonclustered index might be able to remove the corruption on occasion, but that doesn't fix the cause of the corruption.
Just trying to help isn't good enough when helping someone deal with corruption in production - giving the correct solution is.
I'm not trying to sound arrogant or conceited, but an admission of not knowing what the corruption problem is but still offering a solution isn't helpful in my book. Nor is arguing the solution with the person who wrote the code that generated the error being discussed. I hate saying that.
BCP out is a good idea but wouldn't work as the server wouldn't be able to process the corrupt value.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
March 12, 2010 at 7:31 pm
Thanks for the clarifications. It helps.
So, would one answer be to identify the corrupt rows via DBCC PAGE, export the good (and accessible) data only, and re-create the duff data by hand afterward? I could Google for the answer, but I would be interested to hear your thoughts.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 12, 2010 at 11:02 pm
Paul Randal (3/12/2010)
We're talking about table corruption here. The real crux of the matter was 'rebuilding indexes' being offered as a solution without demonstrating understanding of the problem. All caps and asterisks are called for when someone continues to argue something that is wrong.
I didn't argue , nor did I shout out anyones competence or not ...
You asked a question, so I provide an answer for my grounds of providing the info.
Rebuilding a corrupt nonclustered index might be able to remove the corruption on occasion, but that doesn't fix the cause of the corruption.
there is still missing input ... the actual dbcc output to confirm the diagnose and work on the solution ...
Just trying to help isn't good enough when helping someone deal with corruption in production - giving the correct solution is.
I'm not pretending to be ms pss.
I'm not trying to sound arrogant or conceited, but an admission of not knowing what the corruption problem is but still offering a solution isn't helpful in my book. Nor is arguing the solution with the person who wrote the code that generated the error being discussed. I hate saying that.
Whoa ... that's clear .... some people actually read signatures.
BCP out is a good idea but wouldn't work as the server wouldn't be able to process the corrupt value.
Now that's a positive input .... I applaud that !
I've alreaddy stated my gratitude for your numerous contributions at SSC on previous - not so negative - encounters.
Whilest I still respect that fact you help out, it's a shame you fall back to a negative bully attitude
Dude .... move on.
I'm just a mortal dba, I didn't write the code, I just suffer it on a daily basis.
I really hope OP is suffering the actual error, or (s)he for sure will not publish his actual error info, being a fairly new forum member and risking to be flamed or being thrown whatever at her/him.
Robert Davis (3/12/2010)
I could really go for some popcorn while I watch the show.
😉
Thanks to everyone trying to alter the tone of this thread.
Enjoy the weekend.
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
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply